SUMIFS With Multiple Criteria in Excel, and Why It Returns 0

Write SUMIFS with several criteria, a date range and OR logic, then fix the causes of a 0 or a #VALUE!, from text dates to ranges of different sizes.

Founder, Glow Reports. , 3 min read
  • Excel
  • Formulas
  • Reporting Workflows
Rows of a table passing through three stacked filters into a single total, with one rejected row set aside
On this page
  1. How does SUMIFS work with multiple criteria?
  2. How do you use SUMIFS between two dates?
  3. How do you get OR logic with SUMIFS?
  4. Why does SUMIFS return 0?
  5. Why does SUMIFS return #VALUE!?
  6. Does SUMIFS work the same in Google Sheets?
  7. Where does Glow Reports fit?
  8. Common questions

SUMIFS adds the values that meet every condition you give it: a channel, a product, a date range. It's the formula behind most summary blocks in a monthly report. When it returns 0 or a wrong total, the cause is almost always in the data or in how a criterion is written, rarely in the function. Below: how to write SUMIFS with several criteria and dates, how to get OR logic, and the causes of a 0 or a #VALUE! in the order to test them.

How does SUMIFS work with multiple criteria?

SUMIFS takes the range to add first, then pairs of a range to test and a criterion. A row counts only when it meets every criterion, so each pair narrows the total. Excel accepts up to 127 pairs Microsoft Support.

The syntax is . Take an order export with the date in column A, the channel in B, the category in C and the amount in D:

DateChannelCategoryAmount
02/10/2026 09:15OnlineShoes120
05/10/2026 11:00RetailShoes80
14/10/2026 16:40OnlineBags60
20/10/2026 10:05OnlineShoes"35" (text)
31/10/2026 14:32OnlineShoes45
01/11/2026 08:00OnlineShoes200

Online shoe sales are . Put the criteria in cells rather than in the formula, so the same formula serves every channel: .

The criterion can be a value, a cell or a comparison in quotes such as or . Wildcards work on text: matches any category starting with "Sho" Microsoft Support.

How do you use SUMIFS between two dates?

Test the date column twice: once with and the start date, once with and the day after the end date. Join each operator to its date with , so Excel compares against the date and not against the text "G1".

Online shoes in October, 165
=SUMIFS(D:D, B:B, "Online", C:C, "Shoes", A:A, ">=" & DATE(2026, 10, 1), A:A, "<" & DATE(2026, 11, 1))

On the sample, this returns 165: the orders of 2 and 31 October. The text "35" is skipped, as the next section explains. Three details make it hold:

  • builds the date whatever the regional settings Microsoft Support. A typed means 1 October in the US and 10 January in the UK.
  • with the next day keeps orders placed on the last day after midnight. With , the sample returns 120, because the order of 31 October at 14:32 is later than 31 October at 00:00.
  • With the first day of the month in , the end is , and one cell drives the whole month.

How do you get OR logic with SUMIFS?

SUMIFS only does AND. For "Online or Retail", add two SUMIFS, one per value. In Microsoft 365 and Excel 2021, you can also pass both values as an array constant and wrap the result in SUM.

Shoes, Online or Retail, 445
=SUMIFS(D:D, B:B, "Online", C:C, "Shoes") + SUMIFS(D:D, B:B, "Retail", C:C, "Shoes")

The array form is . SUMIFS returns one total per value, and SUM adds them Exceljet. Both return 445 on the sample, all dates included. Don't put both values in one criterion such as . SUMIFS looks for that exact text and returns 0.

Why does SUMIFS return 0?

A 0 means no row met every criterion at once. Test each pair on its own with COUNTIFS on the same ranges: the condition that counts 0 rows is the one that fails Microsoft Support. Then check these causes in order.

  1. Dates stored as text. A date imported as text never meets . Text dates sit on the left of the cell. returns FALSE for them. Convert them with DATEVALUE or DataText to Columns Microsoft Support.
  2. Amounts stored as text. SUMIFS skips text in the sum range without an error, which is why the sample shows 165 and not 200. Convert the column to numbers Microsoft Support.
  3. Hidden spaces. "Online " with a trailing space doesn't match "Online". shows the extra character. Clean the column with TRIM, as shown in how to clean data in Excel.
  4. A criterion written as text. compares against the letters G and 1. Write .
  5. Arguments in SUMIF order. SUMIF puts the sum range last, SUMIFS puts it first. Microsoft names this as a common source of errors when people copy one into the other.

Causes 1 to 3 come from exports more than from typing. A CSV opened with the wrong regional settings often turns dates and amounts into text, as covered in why a CSV opens in one column.

Why does SUMIFS return #VALUE!?

Microsoft lists three causes: ranges of different sizes, a reference to a closed workbook, and a criterion longer than 255 characters Microsoft Support. The first one is by far the most frequent.

  • Different sizes: in , the sum range has 99 rows and the criteria range 119. Use whole columns such as or an Excel table, where every column has the same length.
  • A closed workbook: SUMIFS that points to another file works only while that file is open. Open it and press F9, or copy the data into the report workbook.
  • A long criterion: split a string longer than 255 characters with .

Does SUMIFS work the same in Google Sheets?

Yes. Google Sheets uses the same argument order and the same operators in quotes Google Docs Editors Help. The causes of a 0 are the same, with one difference: Sheets reads dates from a CSV with the spreadsheet's locale, set in FileSettings. A wrong locale gives text dates.

For monthly totals over a whole year, a pivot table grouped by month is often simpler than twelve SUMIFS. How to group a pivot table by month shows the steps.

Where does Glow Reports fit?

SUMIFS works well for a summary block you build once. It gets tiring when every new question needs another formula and another check on dates stored as text.

Glow Reports takes the CSV or Excel export and a question in plain language, such as "online shoe sales by month in 2026". The AI writes the SQL, builds the charts and drafts the text. A CSV upload detects its separator, and the SQL sits under each figure, so you can read which rows were counted and edit the query. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

What is the difference between SUMIF and SUMIFS?

SUMIF takes one criterion and puts the sum range last. SUMIFS takes one or more criteria and puts the sum range first. SUMIFS with a single pair does the same job as SUMIF.

Is SUMIFS case-sensitive?

No. "online" and "Online" match the same rows Exceljet. For a case-sensitive total, use SUMPRODUCT with EXACT.

How do I sum when a cell is not empty?

Use as the criterion: adds the amounts where column B holds something. does the opposite and adds the rows where B is empty.

Why does SUMIFS ignore some rows that look right?

Those rows hold text where SUMIFS expects a number or a date, or a hidden space in the text. Check them with ISNUMBER and LEN.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your CSV or Excel export to Glow Reports and get the figures with the SQL behind each one.

Keep reading