Countifs + date range with reference to date cells

[chloec]

I am not sure how to structure this argument. I need a to return data from a countifs query using a range of dates. Making things complicated is that the range of dates are in two different cells. Can you please help me adjust the formula properly?


My attempt: =COUNTIFS(Lookupbydate!E:E,">="&F2,Lookupbydate!E:E,"<="&F1)

Also - Date is F1 needs to be to the end of the month (F1 & F2 are both dates that are

formatted as the FIRST of every month: M/1/YYYY)

Thanks

 

[SirJB7]

Hi, chloec!

Tried this?

=COUNTIFS("Lookupbydate!E:E",">="&F2,"Lookupbydate!E:E","<="&F1)

Regards!


EDITED and corrected... I hope!

 

[Sajan]

Chloec,

I think you were on the right path with your original formula.

Assuming you want to count the number of dates in E:E that are between F1 and F2,

you can tweak your original formula slightly:

=COUNTIFS(Lookupbydate!E:E,">="&F1,Lookupbydate!E:E,"<"&F2)


Here, I am assuming that F2 is the second date.

For example, if F1 is Feb 1, 2012, F2 is Mar 1, 2012.

If so, the above formula would count as desired.


On the other hand, if you need to count the dates in E:E that fall until end of March (in the above example), then you can tweak the formula as follows:

=COUNTIFS(Lookupbydate!E:E,">="&F1,Lookupbydate!E:E,"<"&DATE(YEAR(F2),MONTH(F2)+1,1))


Hope this helps.


Cheers,

Sajan.

 

[Haseeb A]

=COUNTIF(Lookupbydate!E:E,">="&StartDate)-COUNTIF(Lookupbydate!E:E,">"&EOMONTH(EndDate,0))


Adjust StartDate & EndDate with appropriate reference.

 

[Sajan]

Hi Haseeb,

Thanks for the EOMONTH() function! I had been using the DATE() function to perform date calculations, including finding the last day of a month. I learned something new! (I should take this opportunity to see what other Excel formulas I have "missed"!)


-Sajan.