Spreadsheets

SUMIFS not matching dates: fix the date criteria in your sheet

SUMIFS returning 0 on date criteria? Check text dates, missing ampersand joins and dd/mm versus mm/dd, each with an ISNUMBER test and a fix.

Editorial illustration for SUMIFS not matching dates: fix the date criteria in your sheet

When SUMIFS ignores your date conditions, the cause is nearly always one of three things: the dates in your sheet are text, the criterion was typed without the & that joins the operator to the date, or the day and month are being read the wrong way round. A correct version looks like =SUMIFS($C$2:$C$500,$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<="&$G$1), where F1 and G1 hold real dates.

Cause 1: are the dates real dates or text?

Excel stores a real date as a serial number and shows it formatted. Imported dates are often text that only looks right. Test a cell with =ISNUMBER(A2). TRUE means it is a real date. FALSE means it is text, and any date comparison against it fails silently.

  • For text in a format your computer recognises, convert in a helper column with =DATEVALUE(A2) and format the result as a date.
  • For fixed-length text such as 05/03/2026 meaning 5 March, build the date yourself: =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)).
  • For a whole column in one go, select it, choose Data, Text to Columns, pick Date on the last step, and set the order to DMY if your dates are day first.

Cause 2: what is wrong with ">=F1"?

In a SUMIFS criterion, the operator is text, and the date has to be joined to it. Writing ">=F1" in quotes asks for dates on or after the text F1, which matches nothing. Write ">="&F1. Or build the date inside the formula with DATE: ">="&DATE(2025,4,1). Typing a date as text inside the quotes, such as ">=01/04/2025", is risky because Excel decides how to read it, and the answer depends on regional settings.

Cause 3: is it dd/mm or mm/dd?

A date like 04/05/2026 means 4 May in India and 5 April in a US setting. If a file was made on a machine with different regional settings, Excel may turn some dates into real dates the wrong way and leave others as text, because 25/04/2026 cannot be a month 25. Sort the column. If some dates sit in odd places, you have a mix. Fix at source by re-importing with the right date order, or converting with the DATE formula above.

A worked example with made-up amounts

Say you want the financial year April 2025 to March 2026. Put 1-Apr-2025 in F1 and 1-Apr-2026 in G1.

DateAmount (₹)In the year?
18-Mar-20254,000No, before F1
2-Apr-20251,500Yes
31-Mar-20262,200Yes
1-Apr-2026900No, not before G1

=SUMIFS($B$2:$B$500,$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<"&$G$1) gives ₹1,500 + ₹2,200 = ₹3,700. Using "<" with the next 1 April, instead of "<=" with 31 March, matters if your dates carry a time. A row stamped 31-Mar-2026 at 3 pm is larger than 31-Mar-2026 at midnight and would be dropped.

How do I test the sheet quickly?

  1. =ISNUMBER(A2) on the first few dates.
  2. =COUNT($A$2:$A$500) counts real dates. Compare to the number of rows.
  3. =COUNTIFS($A$2:$A$500,">="&$F$1) as a quick test of the criterion alone.
  4. Add the second criterion only after the first one works.

Does the same fix work in Google Sheets?

Yes. The ISNUMBER test, the & joining and the DATE function behave the same. Sheets may also show a left-aligned date, which is the same warning that the cell is text. Set File, then Settings, and check the Locale, because it decides how a date like 04/05/2026 is read.

If you build the start and end dates from separate cells for year and month, use DATE(year, month, 1) for the start and EDATE(start, 1) for the next month's start. That way a monthly report never depends on someone typing the last day of the month correctly, and February takes care of itself.

When a sheet stops being enough

If statement dates keep arriving in different formats, SpendixAI imports statements from PDF, CSV or Excel and shows the rows for review before saving, so you can see how dates were read before they enter your records.

Checklist

  • ISNUMBER says TRUE for every date.
  • Each criterion joins the operator and cell with &.
  • Start dates use ">=" and end dates use "<" the next day.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports