SUMIF in Excel: Count Values or Sum Them Correctly

Formulas & functions · Updated November 18, 2026

SUMIF adds numbers that meet a condition; it does not count matching cells. To count matches, use COUNTIF for one condition or COUNTIFS for multiple conditions—for example, with categories in A2:A7, =COUNTIF(A2:A7,"Apples") counts the cells labeled Apples.

What SUMIF does in Excel

SUMIF is a worksheet function for conditional addition. It checks each cell in a range against a criterion, then adds the corresponding value from a sum range for every match. It answers questions such as “What is the total sales amount for Apples?” rather than “How many Apple records are there?”

The distinction is easy to miss because “sum if” sounds like a general-purpose conditional calculation. In Excel, however, the function’s result is a sum of numbers, not a tally of matching rows. If the matching amounts are 12, 15, and 0, SUMIF returns 27. It does not return 3 simply because three records matched.

For Microsoft Excel for Microsoft 365 on Windows, the syntax is SUMIF(range, criteria, [sum_range]). The square brackets mean that sum_range is optional; they are part of the syntax description, not characters to type into a formula. If you leave it out, Excel checks and sums the same range.

SUMIF versus COUNTIF: a worked example

Suppose a small order list has category labels in column A and amounts in column B. The values are Apples/12, Bananas/8, Apples/15, Apples/0, Pears/11, and Apples/pending, in rows 2 through 7. The last amount is text, not a number. This example shows why the function choice matters.

  • To total the amounts for Apples, use SUMIF.
  • To count every row labeled Apples, use COUNTIF.
  • To count Apple rows with numeric amounts, use a numeric test as well as the category test.

With category labels in A2:A7 and amounts in B2:B7, =SUMIF(A2:A7,"Apples",B2:B7) returns 27: Excel adds 12, 15, and 0. The text entry pending is not added.

With the same layout, =COUNTIF(A2:A7,"Apples") returns 4. It counts all four matching labels, including the row whose amount is text. COUNTIF examines the specified criteria range, not whether a related cell in another column contains a usable number.

With category labels in A2:A7 and amounts in B2:B7, =SUMPRODUCT((A2:A7="Apples")*ISNUMBER(B2:B7)) returns 3. This counts Apple rows only when the corresponding amount is numeric; zero qualifies as a number. For many everyday counts, COUNTIF or COUNTIFS is simpler, but this formula makes the numeric requirement explicit.

How SUMIF and COUNTIF evaluate criteria

In SUMIF, range is the cells Excel checks, criteria describes what qualifies, and sum_range contains the numbers to add. The corresponding positions in range and sum_range are paired: the first checked cell goes with the first sum cell, the second with the second, and so on. Keep the ranges aligned and the same size to avoid confusing results.

In COUNTIF, the function checks a range and counts cells that meet one criterion. For example, with status values in C2:C100, =COUNTIF(C2:C100,"Complete") counts cells containing Complete. COUNTIF does not need a separate sum range because its result is a count.

Criteria can be typed directly into a formula or built from a cell reference. With amounts in B2:B100 and a threshold in E1, =SUMIF(B2:B100,">"&E1) adds amounts greater than the threshold. With the same layout, =COUNTIF(B2:B100,">"&E1) counts amounts greater than it. The ampersand joins the comparison operator to the value in E1; it is not an addition sign in this context.

Text criteria are usually enclosed in straight double quotes. Comparison criteria such as greater than 100 also go in quotes when written literally, as in =COUNTIF(B2:B100,">100"). When a criterion is a cell reference, join the operator and reference rather than putting the reference itself inside quotes.

Choosing the right counting formula

Start by identifying exactly what “count” means in the question. You may want to count matching labels, matching records with a numeric amount, nonblank cells, or records that meet more than one test. Those are different operations, and selecting the formula based on the intended result prevents a plausible-looking but incorrect answer.

  • Count one condition: use COUNTIF, such as counting a product name or status.
  • Count several conditions: use COUNTIFS, such as counting a product only in one region.
  • Count numeric entries: use COUNT for a range of values; it ignores text and blank cells but counts zero.
  • Count nonempty entries: use COUNTA, remembering that it also counts text and formulas that return an empty text string.
  • Count unique items: use a distinct-value approach rather than assuming COUNTIF removes duplicates.

With product names in A2:A100 and regions in C2:C100, =COUNTIFS(A2:A100,"Apples",C2:C100,"West") counts rows where both conditions are true. COUNTIFS uses a separate range and criterion for each condition. All criteria ranges must have matching dimensions.

For just the number of numeric values in B2:B100, =COUNT(B2:B100) counts numbers, including zero, and ignores text such as pending. If you need to count numeric entries only when a category also matches, the SUMPRODUCT and ISNUMBER pattern in the earlier example is one option.

Text, wildcards, dates, and other criteria

COUNTIF and SUMIF support comparison criteria such as equal to, greater than, less than, and not equal to. They also support wildcards in text criteria: an asterisk represents any number of characters, while a question mark represents one character. To match a literal asterisk or question mark, put a tilde before it.

With names in A2:A100, =COUNTIF(A2:A100,"App*") counts cells whose text begins with App. It can match Apples and Applications, for example, so a wildcard criterion may be broader than a whole-word match. If a count seems unexpectedly high, inspect the criterion and the underlying labels for extra spaces or similar text.

Dates in Excel are stored as serial numbers, so date comparisons can be used as criteria. With dates in A2:A100 and amounts in B2:B100, =SUMIF(A2:A100,">="&DATE(2026,1,1),B2:B100) adds amounts dated January 1, 2026 or later. DATE constructs the date from year, month, and day values, avoiding ambiguity between regional date formats. Adjust the date and ranges to match the reporting period and data.

For two date boundaries or any situation with multiple tests, COUNTIFS and SUMIFS are often more direct than combining several single-condition calculations. SUMIFS adds values meeting multiple conditions; its argument order differs from SUMIF because the sum range comes first. Check the function’s syntax when switching between them.

Common reasons a count or sum looks wrong

A formula can calculate without an error and still answer the wrong question. Check both the function and the data when the result surprises you. In particular, a SUMIF result of zero does not necessarily mean no rows match: the matching rows might have blank or text values in the sum range.

  • Wrong function: SUMIF adds matching values, while COUNTIF counts matching cells. A total is not a record count.
  • Misaligned ranges: make sure each criterion cell is paired with its intended value. Use ranges of equal size and corresponding row positions.
  • Numbers stored as text: text that looks like a number may not behave like a numeric amount in calculations. Convert the source data to numbers if it represents amounts.
  • Unexpected spaces or spelling: “Apples” and “Apples ” can behave differently in exact-match situations. Clean inconsistent imported labels when appropriate.
  • Blank versus zero: a blank amount is not the same thing as a numeric zero. COUNT counts zero, while a count of nonblank cells answers a different question.
  • Criteria punctuation: comparison operators and quotes must be constructed correctly, particularly when joining an operator to a cell reference.

When checking a formula, temporarily filter the source table to the criterion or examine a few matching rows manually. Confirm that the returned figure is supposed to count labels, count numbers, or add amounts. For large or repeated reports, Excel Tables can make source ranges easier to maintain as rows are added, but the underlying choice between SUMIF and COUNTIF remains the same.

Using these formulas in Microsoft Excel

Enter a formula in a blank cell, beginning with an equals sign, and press Enter. In Excel for Microsoft 365 on Windows, formulas shown here use commas between arguments. Some regional settings use semicolons as separators; if Excel rejects a correctly structured formula, check the list separator used by your installation. Function names and the logic are otherwise the same.

Excel for Mac and Excel for the web use the same SUMIF, COUNTIF, and COUNTIFS formulas. The interface and some keyboard shortcuts differ, but no special Mac-only or web-only version of these formulas is needed. If a workbook is shared across locales, Excel may display separators according to each user’s regional settings.

If your source data is an Excel Table, you can also use structured references instead of fixed cell ranges. These can be easier to read and typically expand with the table as records are added. The exact formula depends on the table and column names, so make sure the labels you use match the workbook.

Frequently Asked Questions

Can SUMIF count cells instead of adding them?
Not directly. SUMIF returns a sum of qualifying values. Use COUNTIF for one condition or COUNTIFS for multiple conditions. A helper column containing 1 for each record can be summed, but a count function is usually clearer.

Why does SUMIF return zero when matching rows exist?
SUMIF may find matching criteria but have no numeric values to add in the corresponding sum-range cells. Check that the sum range is correct and that apparent numbers are stored as numbers rather than text. If you want the number of matches, replace SUMIF with COUNTIF or COUNTIFS.

Does COUNTIF count duplicate values?
Yes. COUNTIF counts every cell that meets its criterion, so repeated labels or numbers are counted repeatedly. It does not count distinct items. A unique count requires a separate approach, such as removing duplicates from a copy of the data or using a supported dynamic-array formula.

Can COUNTIF count cells that contain part of a word?
Yes. Use wildcards such as an asterisk around or after the text, depending on the pattern you need. For example, a criterion of *apple* can match text with apple anywhere in the cell. Remember that this may also match longer words or unexpected entries.

How do I count rows that meet two conditions?
Use COUNTIFS and provide a range and criterion for each condition. For example, count a product in a particular region by testing the product column and region column together. The criteria ranges should cover corresponding rows and have the same dimensions.

Is SUMIF different from SUMIFS?
Yes. SUMIF is designed for one criterion, while SUMIFS can apply multiple criteria. Their argument orders are different: SUMIF places the criteria range first, but SUMIFS places the sum range first. Use the function that matches the number of conditions, and verify the argument order when adapting a formula.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.