Excel SUMIFS / date checking help needed!

bazzabear

bazzabear

bazzabear

Soldato
Joined
2 Nov 2013
Posts
4,564
As part of a SUMIFS formula I was hoping to use, it needs to check a column which contains a date, and only SUM if the date is within a certain month.

I was failing to get the formulae to work using < or > and a date in the formula. But if I compared it to the numerical equivalent of that date (e.g. 01May2026 is 46143), it works correctly.

The problem I'm now facing is that I can use that value "46143" if I enter it directly into the formula. But if I enter it into a cell, and use that cell in the formula, it doesn't work.

I.e:

">46143" does return all entries where the date is after 01May.
But
">A1" does not, even though cell A1 contains a value of 46143.

I can't work out why this is the case! Can anyone help? (Or just suggest a more elegant way of doing this in the first place?

Thanks in advance!
 
This is a classic SUMIFS criteria syntax gotcha — you’re very close.
Why ">46143" works but ">A1" doesn’t
When you write:
">46143"
Excel treats that as a text string containing a condition → “greater than 46143”.
But when you write:
">A1"
Excel does NOT evaluate A1 — it literally reads it as the text "greater than A1" (not the value inside A1), so it fails.

✅ The fix: concatenate the operator with the cell reference
You need to join the operator with the value in A1 using &:
">"&A1
So your formula should look like:
=SUMIFS(sum_range, date_range, ">"&A1)

Better approach for “within a month”
Instead of messing with raw serial numbers (like 46143), use actual dates — it’s cleaner and less error-prone.
Example: sum everything in May 2026
=SUMIFS( sum_range, date_range, ">="&DATE(2026,5,1), date_range, "<"&DATE(2026,6,1))
Why this works better:


No reliance on hidden serial numbers


Avoids issues with time values (important!)


Easier to read and maintain



Pro tip (important in real data)
If your dates include times, using:
<= end_of_month
can miss entries on the last day.
That’s why this is safer:
>= first day of month< first day of next month

Quick summary


" >A1 " ❌ → treated as text


" >"&A1 ✅ → evaluates A1


Use DATE() instead of serial numbers for clarity



If you want, I can help you build a version where you just select a month from a dropdown and it automatically sums the right data — very handy for reports.
 
You must log in or register to reply here.
Back
Top Bottom