Forum Discussion
Refering to a date within another cell when using the COUNTIFS formula
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)
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?
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")