How to exclude today's date in a Google Sheets COUNTIFS formula?

Other Questions Google Sheets
1

I’m stuck on a Google Sheets problem. I need to count client cancellations by month but ignore any that happened today. My current formula is:

=ArrayFormula(countif(month(D2:D100),11))

This counts November cancellations, but I can’t figure out how to exclude today’s date (which is in cell A1). I’ve tried using COUNTIFS instead, but no luck. Any ideas on how to add this condition? I’m lost!

My data is in column D, from row 2 to 100. I want to count cancellations for each month, but skip any that match today’s date in A1. Thanks for any help!

2

Another approach you might consider is using the FILTER function in combination with COUNTIF. Here’s a formula that could work for your situation:

=COUNTIF(FILTER(D2:D100, MONTH(D2:D100)=11, D2:D100<>A1), “<>”&“”)

This formula first filters your date range to only include November dates that are not today, then counts the non-empty cells in that filtered range. It’s a bit more explicit in its logic, which can be helpful for troubleshooting or explaining to others.

Remember to adjust the column and row references if your data layout changes. Also, ensure your dates in column D are properly formatted as dates for this to work correctly.

3

I’ve dealt with a similar issue before, and here’s what worked for me:

=COUNTIFS(MONTH(D2:D100),11,D2:D100,“<”&A1)+COUNTIFS(MONTH(D2:D100),11,D2:D100,“>”&A1)

This formula essentially splits the count into two parts: cancellations before today and after today. It avoids the exact match with today’s date in A1.

One thing to keep in mind is that this approach assumes your dates in column D are properly formatted. If you’re having trouble, double-check that your dates are recognized as actual date values by Google Sheets.

Also, you might want to consider using a dynamic range instead of a fixed D2:D100, in case your data grows. Something like D2:D or D2:INDEX(D:D, COUNTA(D:D)) could work better in the long run.

4

hey RunningTiger, try this:

=ArrayFormula(COUNTIFS(MONTH(D2:D100),11,D2:D100,“<>”&A1))

This should count nov cancellations but skip today. the “<>”&A1 part tells it to ignore matches with A1 (today). hope this helps, lemme know if u need anything else!