SUMIF, COUNTIF, SUMIFS and COUNTIFS Practice: 8 Problems Where the Answer Is a Silent Zero

Eight problems built on formulas that return 0 without an error — the failure mode that makes conditional aggregation dangerous. Each group opens with the cause, then gives you a formula to judge.

SUMIF and COUNTIF have one property that makes them worth practising rather than looking up: when they fail, they do not tell you. A criterion that matches nothing returns 0, and 0 is a perfectly plausible total. Nobody notices until a number is reported and someone senior asks why it looks low.

Four causes produce almost all of it. The argument order differs between SUMIF and SUMIFS, so a formula that reads correctly can be testing the wrong column. Criteria containing operators must be quoted, and criteria referring to a cell must be concatenated with an ampersand. The plural forms combine their conditions with AND and offer no OR, which is why date ranges take two conditions and 'North or South' takes two formulas. And numbers stored as text — the standard result of a CSV import — never satisfy a numeric criterion.

Everything below behaves the same in Microsoft 365, Excel 2021 and recent versions, and the same rules apply to AVERAGEIF and AVERAGEIFS. Work each problem before opening the answer.

Four causes, eight problems

The argument order that trips everyone

2 problems

The two functions take their arguments in opposite orders, and this is a historical wart rather than something you can reason out: SUMIF is (range, criteria, [sum_range]) while SUMIFS is (sum_range, criteria_range, criteria, …). Because SUMIFS handles a single condition perfectly well, the simplest policy is to use SUMIFS for everything and never think about the other order again. COUNTIF and COUNTIFS do not have this problem — neither takes a separate sum range.

How it shows up: "SUMIF vs SUMIFS" · "Why does my SUMIF return 0?"

Q1

Column B holds regions and column C holds amounts. You want the total for the North region. Which formula is correct?

=SUMIF(B2:B100, "North", C2:C100)

=SUMIF(C2:C100, "North", B2:B100)
  1. AThe second — the sum range always comes first
  2. BThe first — SUMIF takes the range you test, then the criteria, then the range you add up
  3. CBoth work identically
  4. DNeither; SUMIF cannot take three arguments
▶Show answer & explanation

Answer: B. The first — SUMIF takes the range you test, then the criteria, then the range you add up

🐱 SUMIF checks the first range against the criteria, then adds the matching rows from the third. The second formula looks for the text "North" inside the amounts column, finds nothing, and returns 0 — no error, just a wrong total that looks like a real answer. That silent zero is the reason this ranks as the most expensive beginner mistake in conditional aggregation.

Q2

You need the total of sales that are both in the North region and above 500. Which works?

=SUMIFS(C2:C100, B2:B100, "North", C2:C100, ">500")

=SUMIF(B2:B100, "North", C2:C100) + SUMIF(C2:C100, ">500", C2:C100)
  1. AThe second — add the two conditions together
  2. BThe first — SUMIFS applies all conditions to the same rows; adding two SUMIFs double-counts and answers a different question
  3. CBoth give the same total
  4. DYou need an array formula for this
▶Show answer & explanation

Answer: B. The first — SUMIFS applies all conditions to the same rows; adding two SUMIFs double-counts and answers a different question

🐱 Multiple conditions mean AND on the same row, which is exactly what SUMIFS does. Adding two SUMIFs computes 'North total' plus 'above-500 total' — every North sale above 500 gets counted twice, and sales above 500 outside North get included even though they should not be. Whenever you catch yourself adding conditional sums together, the answer is almost always a single SUMIFS instead.

Writing criteria that actually match

2 problems

Criteria go inside quotes when they contain an operator: ">100", "<>North", ">="&A2 when the value lives in a cell. Bare >100 without quotes is a syntax error, and A2 without the ampersand compares against the literal text. Wildcards work in text criteria — "North*" matches anything starting with North, "?ast" matches East and West — which is occasionally what you want and occasionally the reason a total is too big.

How it shows up: "How do I write greater than in SUMIF?" · "Why does my criteria find nothing?"

Q3

The threshold is stored in cell F1. Which criteria expression is correct?

=SUMIF(C2:C100, ">F1", C2:C100)

=SUMIF(C2:C100, ">"&F1, C2:C100)
  1. AThe first — cell references work directly inside criteria
  2. BThe second — the operator must be joined to the cell value with &, otherwise Excel compares against the literal text "F1"
  3. CBoth work
  4. DYou must use SUMIFS when the threshold is in a cell
▶Show answer & explanation

Answer: B. The second — the operator must be joined to the cell value with &, otherwise Excel compares against the literal text "F1"

🐱 Inside a quoted criteria string, F1 is just two characters, not a reference — so ">F1" asks for values greater than the text "F1", which numbers never are, and you get 0. Concatenating with & builds the string ">500" at calculation time, which is what you meant. This same pattern applies to COUNTIF, AVERAGEIF and their plural forms.

Q4

Column A contains "North", "Northeast" and "Northwest". What does this count?

=COUNTIF(A2:A100, "North*")
  1. AOnly the cells that say exactly North
  2. BAll three — the asterisk is a wildcard matching any characters after North
  3. CNothing; asterisks are not allowed in criteria
  4. DIt returns an error
▶Show answer & explanation

Answer: B. All three — the asterisk is a wildcard matching any characters after North

🐱 Text criteria support wildcards: * stands for any number of characters and ? for exactly one. So "North*" sweeps up Northeast and Northwest as well, which is a common cause of counts that come out higher than expected. For an exact match write "North" with no wildcard; to match a literal asterisk, escape it as ~*.

COUNTIFS and SUMIFS: what multiple criteria actually mean

3 problems

Every criteria pair you add to COUNTIFS or SUMIFS narrows the result, because the conditions are combined with AND on the same row — there is no OR switch. That single fact explains the three questions people ask most. Counting between two dates means two conditions on the same column, not one condition with a range in it. Counting 'North or South' cannot be one COUNTIFS at all. And every criteria range must have the same dimensions as the first, or you get #VALUE! rather than a wrong number — the one case where these functions do warn you.

How it shows up: "COUNTIFS between two dates" · "COUNTIFS with OR" · "Why does COUNTIFS give #VALUE!?"

Q5

Column A holds dates. You want to count the rows that fall in the first quarter of 2026. Which formula does that?

=COUNTIFS(A2:A100, ">=2026-01-01", A2:A100, "<=2026-03-31")

=COUNTIFS(A2:A100, "between 2026-01-01 and 2026-03-31")
  1. AThe second — it reads more naturally
  2. BThe first — a range is two conditions on the same column, and the same column may be listed twice
  3. CNeither; date ranges need SUMPRODUCT
  4. DThe first, but only if the dates are text
▶Show answer & explanation

Answer: B. The first — a range is two conditions on the same column, and the same column may be listed twice

🐱 There is no 'between' operator, so a range is expressed as a lower bound and an upper bound, both pointing at the same column. Listing a column twice looks wrong to people the first time they see it, but it is exactly right — the conditions are ANDed, so the two bounds intersect into the interval you wanted. A safer habit is to put the boundary dates in cells and reference them: ">="&F1 and "<="&F2, which also avoids the ambiguity of how a date literal inside quotes is parsed on a machine with different regional settings.

Q6

You want to count rows where the region is either North or South. What does this return?

=COUNTIFS(B2:B100, "North", B2:B100, "South")
  1. AThe count of North rows plus the count of South rows
  2. B0 — the conditions are ANDed, and no single cell can equal both
  3. C#VALUE!
  4. DThe count of whichever region appears more often
▶Show answer & explanation

Answer: B. 0 — the conditions are ANDed, and no single cell can equal both

🐱 COUNTIFS has no OR. Both conditions must hold on the same row, and one cell cannot be North and South at once, so the honest answer is a silent 0 — the same failure mode as the rest of this page. For OR, add separate COUNTIFs together: =COUNTIF(B2:B100,"North") + COUNTIF(B2:B100,"South"). Note that this is the mirror image of the SUMIFS question earlier: adding conditional counts is right for OR and wrong for AND, and telling the two apart is the whole skill.

Q7

The data starts on row 2. This formula returns #VALUE! instead of a number. What is wrong?

=COUNTIFS(B2:B100, "North", C2:C99, ">500")
  1. AThe second criteria needs to be a number, not text
  2. BThe two criteria ranges are different sizes — 99 rows against 98
  3. CCOUNTIFS accepts only one criteria pair
  4. DThe ranges must be absolute references
▶Show answer & explanation

Answer: B. The two criteria ranges are different sizes — 99 rows against 98

🐱 Every criteria range in COUNTIFS and SUMIFS must have the same number of rows and columns as the first one, because the function walks them in lockstep row by row. C2:C99 is one row short of B2:B100, so there is no consistent row to evaluate and Excel raises #VALUE!. This mismatch is easy to create by dragging one range's handle, and it is worth noticing that this is the one error in this family that surfaces loudly — mismatched sizes give you an error, while mismatched types and swapped ranges give you a plausible wrong number.

When the answer is 0 but the data is fine

1 problem

Conditional functions never tell you that nothing matched — they return 0, which is indistinguishable from a genuine zero total. Two causes dominate: type mismatch (numbers stored as text after a CSV import, so a numeric criterion never fires) and invisible characters, usually trailing spaces or non-breaking spaces pasted from a web page. Both make cells that look identical behave as different values.

How it shows up: "COUNTIF returns 0 but I can see matching rows"

Q8

The amounts were imported from a CSV and are left-aligned in their cells. This returns 0 even though many values exceed 500. Why?

=COUNTIF(C2:C100, ">500")
  1. ACOUNTIF cannot use comparison operators
  2. BThe values are stored as text, and text is never greater than a number for the purposes of this comparison
  3. CThe range needs to be absolute
  4. D500 must be written as "500"
▶Show answer & explanation

Answer: B. The values are stored as text, and text is never greater than a number for the purposes of this comparison

🐱 Left alignment is the visual tell: Excel aligns text left and numbers right by default, so a column of left-aligned figures is a column of text. Numeric criteria then match nothing and you get a silent 0. Fix the data rather than the formula — Text to Columns with default settings, or multiply by 1, or use Paste Special → Multiply by 1 across the range. Getting into the habit of glancing at alignment catches this in a second.

Keep going

Excel formulas practice (8 problems)VLOOKUP failures, dollar signs and error values — the rest of the formula traps.INDEX MATCH vs VLOOKUP vs XLOOKUPWhich lookup to use, and what still breaks after you switch.SQL interview questionsThe same conditional-aggregation ideas in a database: WHERE, HAVING and GROUP BY.

SUMIF and COUNTIF — FAQ

What is the difference between SUMIF and SUMIFS?

SUMIFS handles multiple conditions, but the practical difference is argument order: SUMIF is (range, criteria, sum_range) while SUMIFS is (sum_range, criteria_range, criteria). Since SUMIFS handles one condition perfectly well, using it everywhere means only one order to remember.

Why does my SUMIF return 0?

Most often the sum range and criteria range are swapped, the criteria string is malformed (a cell reference not joined with &), or the numbers are stored as text after an import. Check cell alignment first — text aligns left, numbers right.

How do I write a criterion like 'greater than the value in F1'?

Join the operator to the reference: ">"&F1. Writing ">F1" compares against the literal two-character text F1, which no number is greater than, so you get 0.

Do wildcards work in COUNTIF?

Yes, in text criteria: * matches any run of characters and ? matches exactly one. "North*" therefore also counts Northeast and Northwest — a frequent cause of counts that come out too high. Escape a literal asterisk as ~*.

How do I use COUNTIFS between two dates?

Write two conditions on the same column — a lower bound and an upper bound: =COUNTIFS(A2:A100,">="&F1,A2:A100,"<="&F2). There is no 'between' operator, and listing the same column twice is correct because the conditions are ANDed. Putting the boundary dates in cells and referencing them also avoids regional-settings ambiguity in how a quoted date literal is parsed.

Can COUNTIFS do OR instead of AND?

No. Every criteria pair narrows the result, so COUNTIFS(B:B,"North",B:B,"South") returns 0 — no cell is both. For OR, add separate COUNTIFs: =COUNTIF(B:B,"North")+COUNTIF(B:B,"South"). This is the mirror image of the AND case, where adding conditional sums double-counts and a single SUMIFS is correct.

Why does COUNTIFS return #VALUE!?

Almost always because the criteria ranges are different sizes — for example B2:B100 against C2:C99. Every criteria range must have the same number of rows and columns as the first, because the function compares them row by row in lockstep. This is the one error in this family that surfaces loudly instead of returning a plausible wrong number.

Can SUMIF add up cells based on their colour?

No. Conditional functions read values, not formatting. Colour-based totals need a helper column that records the meaning behind the colour, which is the better design anyway because the meaning becomes data rather than decoration.