COUNTIFS Formula with Criteria to match current year and month

0
votes

I'm trying to only count cells based on a date value being equal to the current month and year. Does an operator for this exisit? I tired to replace the ">=" below to "=" but it returned no values. In contrast he ">=" operator is returning more values that it should. Does anyone know how i can revise the formula below to only count the cell range if values in Cells P21:P502 are equal to the current Month and Year? The last two criteria in the formula below is all I cant seem to get working right

=COUNTIFS('CSAT Risk Register'!$O$21:$O$502,"*",'CSAT Risk Register'!$L$21:$L$502,B6,'CSAT Risk Register'!$I$21:$I$502,"*",'CSAT Risk Register'!$P$21:$P$502,">43831",'CSAT Risk Register'!$P$21:$P$502,"=="&YEAR(TODAY()),'CSAT Risk Register'!$P$21:$P$502,"=="&MONTH(TODAY()))
1
excel

1 Answers

0
votes
  1. Excel does not use == just one will suffice.
  2. You need to bookend the dates, use EOMONTH().
=COUNTIFS('CSAT Risk Register'!$O$21:$O$502,"*",'CSAT Risk Register'!$L$21:$L$502,B6,'CSAT Risk Register'!$I$21:$I$502,"*",'CSAT Risk Register'!$P$21:$P$502,">="&EOMONTH(TODAY(),-1)+1,'CSAT Risk Register'!$P$21:$P$502,"<"&EOMONTH(TODAY(),0)+1)