Forum Discussion
Not able to count with COUNTIFS
Hello beeliew,
The reason your formula is returning 0 is because COUNTIFS doesn’t evaluate the calculated column with TEXT() properly. When you use =TEXT([@[Date: Referral Rx]], "mmmm"), Excel is storing those as dynamic formulas, not static text values, so the comparison against TEXT(TODAY(),"mmmm") fails.
A better approach is to count directly from the date column instead of relying on the text month column. You can do this with a date-based comparison:
=COUNTIFS(
Table[Date: Referral Rx], ">=" & EOMONTH(TODAY(),-1)+1,
Table[Date: Referral Rx], "<=" & EOMONTH(TODAY(),0)
)This counts all rows where the referral date falls in the current month.
If you prefer to use a helper column, use =MONTH([@[Date: Referral Rx]]) instead of TEXT(), and then compare with MONTH(TODAY()):
=COUNTIFS(Table[MonthNumber], MONTH(TODAY()))Both methods will give you the correct count for the current month. The date-based version is usually the most reliable since it avoids text conversion issues.
Olufemi7 , initial formula works fine if done correctly, see Harun24HR example.
Thank you SergeiBaklan for pointing me to Harun24HR example. I see that the initial formula with TEXT() can work if the table column is structured correctly. In my earlier reply I explained why the formula was returning 0 in this case and suggested a date‑based approach as a more reliable alternative.
Microsoft’s documentation confirms that COUNTIFS works best with numeric or date ranges rather than text conversions, which can cause mismatches. For reference, see:
Both methods are valid the TEXT() approach works when implemented properly, and the date‑based comparison is the documented, more robust solution.