Forum Discussion

beeliew's avatar
beeliew
Copper Contributor
Jan 24, 2026

Not able to count with COUNTIFS

I am having problem counting with COUNTIFS

 

In a column of an Excel table

=TEXT([@[Date: Referral Rx]], "mmmm")

This shows the month in the column

 

Then, I am counting the total of those in the current month with the following code:

=LET(

  targetDate, TEXT(TODAY(), "mmmm"),

      COUNTIFS(

        Table[Month: Initial Referral Rx], targetDate

)

 

It is not counting it. I get 0. I tried

      COUNTIFS(Table[Month: Initial Referral Rx], TEXT(TODAY(), "mmmm"))

It also didn't work. Need help. Thank you in advanced.

excel
Excel on Mac

5 Replies

  • Olufemi7's avatar
    Olufemi7
    Iron Contributor
    Jan 27, 2026

    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's avatar
        Olufemi7
        Iron Contributor
        Jan 28, 2026

        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.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 26, 2026

    The reason your formula is returning 0 is likely due to data type mismatches or language/region settings, rather than a syntax error.

    When you use the TEXT function to convert a date to a word (e.g., "October"), Excel creates a specific text string. However, the TEXT function is highly sensitive to your Mac's Region and Language settings.

     

    Solution 1

    Assuming your original Date column is named Date: Referral Rx, use this formula:

    =COUNTIFS(

        Table[Date: Referral Rx], ">=" & EOMONTH(TODAY(), -1) + 1,

        Table[Date: Referral Rx], "<=" & EOMONTH(TODAY(), 0))

     

    Solution 2: Fix for your Current Formula

    for the most reliable result, as it avoids language and formatting issues entirely. If you must use text

    =LET(

      currentMonth, TRIM(TEXT(TODAY(), "[$-en-US]mmmm")),

      COUNTIFS(Table[Month: Initial Referral Rx],currentMonth))

     

    Solution 3: Direct Date Comparison

    Count dates falling in the current month without converting to text:

    =COUNTIFS(

      Table[Date: Referral Rx],

      ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1),

      Table[Date: Referral Rx],

      "<="&EOMONTH(TODAY(), 0))

     

    …recommendation

    Do NOT store month names as text

    Instead, count by dates, which are reliable.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.