COUNTIF Excel: How to Count Matches and Sum Values

Formulas & functions · Updated November 18, 2026

COUNTIF in Excel counts cells that meet one condition; it does not add the values in those cells. To add values that match a condition, use SUMIF for one condition or SUMIFS for multiple conditions.

What COUNTIF Does in Excel

COUNTIF answers a counting question: how many cells in a range satisfy a condition? You can use it to count how many orders came from a region, how many scores passed a threshold, or how many cells contain a particular word. The result is a number of matching cells, not the total of their contents.

The distinction matters when your data contains amounts. If three matching rows contain 120, 150, and 110, COUNTIF returns 3. It will not return 380. To total those amounts according to a condition in another column, use SUMIF.

In Microsoft 365 for Windows, enter a formula in a blank cell and press Enter. The same worksheet formulas work in Excel for Mac and Excel for the web; the interface can differ, but the function’s purpose does not. A useful way to decide is to ask whether the answer should be a number of records or a sum of their numeric values.

Count Matching Cells with COUNTIF

For a concrete example, assume cells A2:A8 contain the regions North, South, North, West, South, North, and East. To count the North entries, enter this formula in any empty cell:

With region names in A2:A8, =COUNTIF(A2:A8,"North") returns 3, because North appears three times.

The first argument is the range to inspect, and the second is the criterion to apply. The criterion can be a text value, a number, a comparison such as greater than a threshold, or a reference to another cell. COUNTIF checks each cell in the range and counts the cells that satisfy that one criterion.

If the criterion is likely to change, put it in a cell rather than typing it into the formula. With region names in A2:A8 and the word North in D2, =COUNTIF(A2:A8,D2) also returns 3. Changing D2 to South makes the result 2 without editing the formula. This approach is handy for report controls and small dashboards.

Use SUMIF When You Need a Total

SUMIF evaluates a condition in one range and adds the corresponding values from a sum range. Suppose A2:A8 contains North, South, North, West, South, North, and East, while B2:B8 contains 120, 80, 150, 90, 70, 110, and 60. The amounts associated with North are 120, 150, and 110.

With regions in A2:A8 and amounts in B2:B8, =SUMIF(A2:A8,"North",B2:B8) returns 380. The arithmetic is 120 + 150 + 110 = 380. The first range is where Excel looks for North; the final range supplies the values to add. The matching positions in those two ranges must correspond—for example, A2’s condition is paired with B2’s amount.

If the cells you are evaluating are also the numbers to add, the sum range can be omitted. For example, you can use SUMIF to total values above a threshold within a numeric range. But when one column contains categories and another contains amounts, include the amount column as the sum range. COUNTIF has no sum-range argument, which is why it cannot perform that job.

Count or Sum with Numeric Conditions

Criteria can include comparison operators such as greater than, less than, or equal to. When an operator and a value are typed directly as a criterion, put them inside quotation marks. This lets Excel interpret the criterion as a condition rather than as ordinary text.

With amounts in B2:B8 containing 120, 80, 150, 90, 70, 110, and 60, =COUNTIF(B2:B8,">=100") returns 3: 120, 150, and 110 meet the condition. To total those qualifying amounts instead, =SUMIF(B2:B8,">=100") returns 380, the same three values added together.

A criterion can also refer to a value in a cell. When you need to combine an operator with a cell reference, join the operator text to the reference with Excel’s ampersand operator. This is useful when a user should be able to adjust a cutoff without changing the formula. Be careful not to put the cell reference itself inside quotation marks, or Excel will treat it as literal text.

Apply More Than One Condition

COUNTIF handles one condition. For a count that must satisfy several conditions at once, use COUNTIFS. For a total that must satisfy several conditions, use SUMIFS. These functions are useful when a report needs to narrow results by both category and location, or by product and date.

For example, suppose A2:A7 contains East, East, West, East, West, East; B2:B7 contains Hardware, Software, Hardware, Hardware, Software, Software; and C2:C7 contains 100, 200, 150, 80, 90, 120. There are two East rows labeled Hardware, with amounts of 100 and 80.

With regions in A2:A7, product types in B2:B7, and amounts in C2:C7, =COUNTIFS(A2:A7,"East",B2:B7,"Hardware") returns 2. With those same ranges, =SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Hardware") returns 180, because 100 + 80 = 180. SUMIFS starts with the sum range, followed by condition-range and criterion pairs; COUNTIFS uses condition-range and criterion pairs.

For COUNTIFS and SUMIFS, keep the related ranges aligned and the same size. A range that starts on row 2 should normally be paired with ranges that also start on row 2 and cover the same number of rows. Mismatched ranges can produce errors or results that do not reflect the records you intended to compare.

Text Criteria, Wildcards, and Dates

Text criteria are generally not case-sensitive: a criterion for North will match north as well as North. To find text that contains a pattern rather than an exact entry, COUNTIF and SUMIF support wildcards. An asterisk represents any number of characters, a question mark represents one character, and a tilde lets you search for a literal wildcard character.

With last names in A2:A5 containing Johnson, Mason, Wilson, and Patel, =COUNTIF(A2:A5,"*son") returns 3. The asterisk allows any characters before “son.” A criterion such as a single-character wildcard can help when entries vary by one character, while a tilde is useful if the data itself contains an asterisk or question mark that you want to match literally.

Dates are stored as serial values in Excel, so criteria can compare dates just like other numbers. It is usually clearer to build a date criterion with the DATE function than to rely on how a typed date is interpreted under different regional settings. With dates in A2:A5 equal to January 5, 2026; December 31, 2025; February 14, 2026; and January 1, 2026, =COUNTIF(A2:A5,">="&DATE(2026,1,1)) returns 3. The three dates on or after January 1, 2026 are counted.

Common COUNTIF and SUMIF Problems

A formula can look right and still return an unexpected result. Check the data and the criterion as well as the function name. These are common causes:

  • Using COUNTIF for a total: COUNTIF counts qualifying cells. Replace it with SUMIF or SUMIFS when the result should add amounts.
  • Numbers stored as text: Values that look numeric may be text, especially after importing data. They may not behave as expected in calculations or comparisons. Check the cell format and, more importantly, whether Excel recognizes the entries as numbers.
  • Extra spaces or inconsistent labels: “North” and “North ” can be different text entries. Imported data may include leading or trailing spaces that make an exact match miss some rows.
  • Incorrect comparison criteria: Operators such as > and < need to be included in the criterion. When combining an operator with a cell reference, join them rather than enclosing the reference in quotation marks.
  • Misaligned ranges: In SUMIF, make sure each row in the criteria range corresponds to the same record in the sum range. For multiple conditions, use ranges of matching size.
  • Regional formula separators: The examples here use commas, as requested for standard English-language Excel syntax. Some regional Excel settings display semicolons between function arguments; if Excel rejects a formula with commas, check the list-separator setting used by that installation.

Also confirm that the chosen range includes all relevant rows. A formula referencing A2:A100 will not count a new entry typed in A101. If the data is an Excel table, structured references can help formulas expand with new rows, although the exact table formula depends on the table’s column names.

Choosing the Right Function

Pick the function based on the result you need, not just on the fact that you have a condition. COUNTIF and SUMIF are related, but they answer different questions:

  • Use COUNTIF to count cells matching one condition, such as the number of open tasks.
  • Use SUMIF to add values matching one condition, such as the sales total for one region.
  • Use COUNTIFS to count records that match two or more conditions, such as open tasks assigned to one team.
  • Use SUMIFS to total values that match two or more conditions, such as sales for one region and product.

For a one-off investigation, filtering a list and reviewing the visible rows may be enough. For a repeatable report, a formula updates as the underlying values change. A PivotTable is another useful option for summarizing larger datasets across several categories, especially when readers need to rearrange or explore the summary. It is not a replacement for every formula, but it can be more convenient when the question changes often.

Frequently Asked Questions

Can COUNTIF add numbers in Excel?
No. COUNTIF returns the number of cells that meet one condition. Use SUMIF to add matching values, or SUMIFS when the total depends on multiple conditions.

What is the difference between COUNTIF and SUMIF?
COUNTIF counts qualifying cells. SUMIF adds values associated with qualifying cells. For example, a region count might be 3 while its sales total is 380.

How do I count cells that are not blank?
Use a COUNTIF criterion for nonblank cells. Be aware that formulas returning an empty text string can make a cell appear blank even though it contains a formula; if that distinction matters, choose a counting method appropriate to the data.

Can COUNTIF use two conditions?
Not by itself. Use COUNTIFS for two or more conditions, supplying a range and criterion for each condition.

Why does COUNTIF return zero when the value is there?
Check for extra spaces, different spelling, a mismatched number-versus-text value, or a criterion that is not written as intended. If you are using a wildcard, confirm that its position matches the pattern you want.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive. If the count must distinguish capitalization, use a different approach designed for case-sensitive comparison rather than relying on COUNTIF.

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.