Not able to count with COUNTIFS

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.

This works fine to me.

=LET(targetDate, TEXT(TODAY(), "mmmm"),COUNTIFS(Table1[Month: Initial Referral Rx], targetDate))