COUNTIF counts the cells in a range that meet a condition; SUMIF adds up the values that do. =COUNTIF(A2:A7, "Pens") counts the rows whose product is Pens, and =SUMIF(A2:A7, "Pens", C2:C7) totals their sales. Each tests exactly one condition. For more than one, you want their plural versions, COUNTIFS and SUMIFS. To count with no condition at all, use the plain COUNT, COUNTA, and COUNTBLANK.
That’s the happy path. This page also covers the unhappy one, because this family of functions has a talent for failing quietly: a criteria missing its quotation marks, a cell reference missing an &, or numbers stored as text will all produce a confident 0 instead of an error.
How does COUNTIF work?
COUNTIF takes two arguments, the range to look in and the criteria to test: =COUNTIF(range, criteria). The criteria can be a number, an expression, a cell reference, or a text string, so all of these are valid: the number 32, a comparison like ">32", a cell like B4, or a word like "apples".
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Sales | Count | |
| 2 | Pens | North | 120 | 3 | |
| 3 | Paper | South | 80 | ||
| 4 | Pens | North | 200 | ||
| 5 | Ink | North | 150 | ||
| 6 | Paper | South | 90 | ||
| 7 | Pens | South | 60 |
One thing Microsoft is explicit about: “COUNTIF uses only a single criteria.” One range, one test. Two conditions at once is COUNTIFS’s job, covered below.
What can go in the criteria argument?
Three rules cover most of the confusion.
First, quotation marks. Text criteria, and any criteria containing a logical or mathematical symbol, must be enclosed in double quotation marks; a plain number needs none. So =COUNTIF(C2:C7, ">100") counts the sales over 100 in the table above (120, 200, and 150, so 3), and =COUNTIF(C2:C7, 120) matches the bare number 120 without quotes. The supported comparison operators include =, <> (not equal), <, and >, and they always ride inside the quotes with their value.
Second, wildcards. A question mark matches any single character and an asterisk matches any sequence of characters:
counts every product starting with P, the three Pens rows plus the two Paper rows, so 5. =COUNTIF(A2:A7, "?????") counts entries exactly five characters long, which here is the two Paper rows. To match a literal ? or *, put a tilde in front of it: "~?".
Third, case doesn’t matter: a criteria of "apples" and a criteria of "APPLES" match the same cells.
How does SUMIF work, and what does sum_range do?
SUMIF adds a third, optional argument: =SUMIF(range, criteria, [sum_range]). The criteria is tested against range; the values actually added come from sum_range.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Sales | Total | |
| 2 | Pens | North | 120 | 380 | |
| 3 | Paper | South | 80 | ||
| 4 | Pens | North | 200 | ||
| 5 | Ink | North | 150 | ||
| 6 | Paper | South | 90 | ||
| 7 | Pens | South | 60 |
Leave sum_range out and Excel adds the cells of range itself. =SUMIF(C2:C7, ">100") tests and sums the same column: 120 + 200 + 150 = 470.
File away where that sum range sits: third, at the end. SUMIFS moves it, and that move breaks real spreadsheets.
How do I use COUNTIF or SUMIF with multiple criteria?
You don’t; you switch functions. COUNTIFS and SUMIFS accept up to 127 range and criteria pairs, and a row counts only when every pair passes. That’s AND logic: all the criteria must be met at once.
Against the table above, that requires the product to be Pens and the region to be North. Rows 2 and 4 qualify, so the count of both conditions together is 2, not the 3 that Pens alone would give.
The pairs can even test the same column twice, which is how you count values inside a numeric window:
counts sales from 100 to 150 inclusive: 120 and 150, so 2.
SUMIFS does the same job for totals, with the argument-order twist:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Sales | Total | |
| 2 | Pens | North | 120 | 320 | |
| 3 | Paper | South | 80 | ||
| 4 | Pens | North | 200 | ||
| 5 | Ink | North | 150 | ||
| 6 | Paper | South | 90 | ||
| 7 | Pens | South | 60 |
Microsoft calls that order difference a common source of problems with these functions, and it earns the billing. Convert a SUMIF to a SUMIFS by bolting on a second condition without reordering, and your criteria now point at the wrong ranges.
One more documented rule: in COUNTIFS, each additional range must have the same number of rows and columns as the first, and in SUMIFS each criteria range must match the sum range’s shape. What happens when SUMIF and SUMIFS ranges don’t line up is covered in the failure list below.
How do I get OR logic instead of AND?
COUNTIFS and SUMIFS only do AND. For “this or that”, add separate COUNTIFs:
Three Pens rows plus one Ink row gives 4. The addition trick works because a cell can’t be Pens and Ink at once. If your criteria can overlap, say "P*" plus "Pens", the overlapping rows are counted twice, so either make the criteria mutually exclusive or subtract the overlap. The same pattern with SUMIF adds the two subtotals.
If what you actually need is a per-row verdict rather than a count, that’s the IF function’s job, not COUNTIF’s.
Why is my COUNTIF or SUMIF not working or returning 0?
Almost always the formula is doing exactly what you wrote, which isn’t what you meant. Work down the list; the first three produce a clean-looking 0 with no error at all.
1. The criteria is missing its quotation marks
Microsoft’s troubleshooting table for SUMIFS opens with exactly this symptom, a zero shown instead of the expected result, and its first fix is to put the criteria in quotation marks. The quote rule from earlier applies to the whole family: text and anything with an operator goes in double quotes, ">100", not >100.
2. The criteria references a cell but is missing the &
A criteria that compares against another cell has to be assembled from two parts: the operator in quotes, the cell reference outside them, joined by &. Microsoft’s own SUMIF example is built the same way: =SUMIF(A2:A5, ">" & C2, B2:B5) sums the commissions for property values greater than the value in C2 (their four-row example table, not the one above). In our table, with a threshold of 100 typed in G1:
Write it as one string, ">G1", and the criteria no longer refers to the cell at all. Against this table of numbers it reports 0 and raises no error, because as far as Excel is concerned nothing went wrong.
3. The numbers are stored as text
SUMIF’s documentation says the cells it works on “must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.” A numeral stored as text is a text value, so SUMIF skips it, and the total comes out low, or 0, with nothing flagged. Excel usually marks these cells with a small green triangle; select them and use Convert to Number, then watch the total change. The same type mismatch is a top cause of lookup failures too, as covered in why your VLOOKUP returns #N/A.
4. You carried SUMIF’s argument order into SUMIFS
Covered above, and worth rechecking whenever a formula was converted from one function to the other: SUMIF ends with the sum range, SUMIFS starts with it.
5. The ranges are different sizes
In SUMIF, a sum_range that doesn’t match range in size still calculates. Microsoft’s SUMIF page spells out what happens instead: “the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range.” In other words, Excel quietly slides a same-shaped block from the top of your sum range and adds that, so the answer is wrong by an offset instead of visibly broken. SUMIFS is stricter: mismatched shapes return #VALUE!, which at least tells you something is off.
6. Stray spaces, curly quotes, or a 255-character string
Microsoft’s COUNTIF best practices warn about leading spaces, trailing spaces, a mix of straight and curly quotation marks, and nonprinting characters, all of which interfere with matching text. And there’s a hard limit: matching a string longer than 255 characters returns incorrect results, a warning documented for COUNTIF, SUMIF, and SUMIFS alike. The workaround is to shorten the string, or split the match across multiple strings joined with & or CONCATENATE.
Why does it show #VALUE! instead of a number?
A #VALUE! from this family usually means a reference to a closed workbook: Microsoft documents the identical cause and error for COUNTIF/COUNTIFS and for SUMIF/SUMIFS. The documented fix is to open the linked workbook and press F9 to refresh the formula. The other documented cause, for SUMIFS, is the range-size mismatch from cause 5 above. For the rest of Excel’s error vocabulary, see every Excel error explained.
The wrong total that looks right
An error value gets fixed, because everyone can see it. The failures worth losing sleep over are the ones above that don’t error: the SUMIF that ignores a column of text-numerals, the criteria string that matches nothing, the sum range that slid three rows down. Each returns a plausible number, the plausible number lands in a total, and the total looks fine. Nobody audits a number that looks fine.
So when a workbook that other people rely on changes, don’t trust the eyeball check. Compare the two versions and read exactly which cells and formulas changed, then make sure the number moved because the business did, not because a criteria quietly stopped matching.
