Fix the 0 error

What 0 means

SUMIFS returns 0 when none of the rows satisfy all criteria simultaneously. Because SUMIFS applies AND logic — every criteria_range/criteria pair must match the same row — adding more conditions narrows the result, and a mismatch in any one condition causes the whole row to be excluded.

Common causes

• The criteria are mutually exclusive or cover different rows — for example, filtering for region 'North' AND region 'South' in the same range always returns 0 because no row can be both.
• The sum_range is not the first argument — SUMIFS argument order is (sum_range, criteria_range1, criteria1, ...) which is the reverse of SUMIF.
• One of the criteria_range columns contains text-numbers while the corresponding criteria is a real number, preventing any match.
• A date criteria is written as a text string like "2024-01-01" but the range contains serial date numbers — string comparison fails.
• All criteria_ranges and sum_range must be the same size; a range that is shorter cuts off rows and can return 0 for data in excluded rows.

Example fix

The broken formula applied two criteria to the same column (A) with mutually exclusive values. The fixed version filters A for region 'North' AND B for quarter 'Q1', which is logically achievable on the same row.

How to fix it

1. 1Verify each condition independently with COUNTIFS using one criteria pair at a time. Start with =COUNTIFS(B:B,"North") then add pairs to find which condition eliminates all rows.
2. 2Check the argument order: SUMIFS is =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The sum_range comes first, unlike SUMIF where it is last.
3. 3Convert text-numbers in criteria ranges using VALUE() or Paste Special > Multiply. Confirm with =ISNUMBER(B2) across the range.
4. 4For date criteria, use DATE() or a cell reference containing a real date: =SUMIFS(C:C,A:A,">="&DATE(2024,1,1),A:A,"<"&DATE(2024,2,1)).
5. 5Ensure all range arguments cover exactly the same rows. Use the same row numbers for sum_range and every criteria_range, and lock them with $ to avoid drift when copying.

Frequently asked