Refering to a date within another cell when using the COUNTIFS formula

[Tyrell Williams]

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?

[#2]

Could be

=SUMPRODUCT((MONTH(range)=2)*(YEAR(range)=2017))

[#3]

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.

[#4]

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)

[#5]

Sorry, I'm a bit confused, my current formula is =COUNTIFS(range,"name", range2,"place", range3 ,"2/22/2017")

i get a count when I put and exact date for the criteria, but i need a way to do it month by month

should I just include the statements you wrote previously with the same range for the two criteria? 

[#6]

I was bit wrong with date constants if you hardcode them, but the idea is the same

=COUNTIFS(range,"name", range2,"place", range3 ,">=2017-02-01", range3 ,"<=2017-02-08")