SUMIFS with date range as a cell reference

[malagajm]

Hi All,
I am working with the formula below, to add a list of values (Column A), in a particular month (Date in column B), that doesn't have a status of cancelled (Column C) and has a status of Revenue (Column D).
This is working fine with no issues:

=SUMIFS(A:A,B:B,">="&DATE(2016,3,1),B:B,"<="&DATE(2016,3,31),C:C,"<>"&"Cancelled",D:D,"Revenue")

The only problem is that I have to set up the same formula again for each month, this requires changing the date 12 times, which is not ideal.
I am trying to set it up in a way that I have a cell reference containing a date range, for example for April, it would be the 1/04/16 to 30/04/16.
In this way all I have to do is change the Month name in my reference cell and the formula will automatically calculate the total for the desired month.

I don't even know how to begin setting this up, would anyone here have any idea or have dealt with something similar before?
I would imagine something along these lines

=SUMIFS(A:A,B:B,"="&F1,C:C,"<>"&"Cancelled",D:D,"Revenue")

Where F1 is my reference cell and I manually type in the month

Many thanks in advance

[arlu1201]

Hello malagajm,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

[usmanniazi84]

just new and also need help on sumifs