Forum Discussion
Refering to a date within another cell when using the COUNTIFS formula
Hello
Im trying to use countifs and one of the criteria is a date. I just need the month really. The dates are formatted as MM/DD/YYYY but I cant seem to write the proper function. I tried countifs( range, "2/??/2017) but is doesn't work. I also tried countifs(range, "2*) but it also doesn't work. Any help?
Could be
=SUMPRODUCT((MONTH(range)=2)*(YEAR(range)=2017))
Will this work? What im doing is counting the number of entries by specific person in a specific place in a specific month. Its just that the dates are all MM/DD/YYYY. and for some reason I cant us "2*" or "2/??/2017" as my criteria for the range in which the dates are held.
Hi Tyrell,
To SUMPRODUCT you may add as many criteria as you need, if they are with AND condition.
In COUNTIFS you try to compare each date in the range (which is number) with some text, that never returns TRUE. You may add couple of conditions into COUNTIFS
=COUNTIFS(range,">=" & DATEVALUE("2017-02-01"),range,"<="&EOMONTH(DATEVALUE("2017-02-01"),0))or like, where you compare dates with dates (aka numbers with numbers)
