Excel COUNTIF and SUMIF Examples That Actually Work

In Excel, use COUNTIF(range, criteria) to count cells that meet one condition and SUMIF(range, criteria, sum_range) to add values associated with matching cells. For example, if A2:A8 contains regions and B2:B8 contains sales, COUNTIF can count North entries and SUMIF can total their sales; the examples below show how to handle text, numbers, dates, wildcards, and multiple conditions.

What COUNTIF and SUMIF do

COUNTIF and SUMIF are conditional functions: each checks a range against a criterion, then returns a count or a sum. They are useful for everyday tasks such as counting completed orders, totaling sales for one department, or finding how many invoices exceed a chosen amount. Both functions are available in Excel for Microsoft 365 on Windows, and their basic formulas work in Excel for Mac and Excel for the web as well.

COUNTIF has two arguments: the cells to inspect and the condition to test. SUMIF adds a third argument for the values to total. In SUMIF, the criterion range and sum range should generally have the same shape and corresponding rows. If the sum range is omitted, Excel adds the cells in the criterion range itself when they meet the condition.

These functions test one condition at a time. For two or more conditions, use COUNTIFS or SUMIFS, covered below. The “IF” in the names does not mean that these functions return TRUE or FALSE; they return a number.

Basic COUNTIF examples

Suppose A2:A8 contains North, South, North, East, North, South, and North. COUNTIF can count how often a region appears. With those values in A2:A8, this formula returns 4:

With region names in A2:A8, the formula counts cells equal to North: =COUNTIF(A2:A8,"North"). The result is 4.

You can also refer to a cell for the criterion, which is convenient when users need to change the selection without editing the formula. If E2 contains North and the same region list is in A2:A8, =COUNTIF(A2:A8,E2) returns 4. This is an exact text match apart from capitalization: COUNTIF is not case-sensitive, so “north” and “North” are treated alike.

For numeric conditions, put comparison operators inside quotation marks. If B2:B8 contains 120, 90, 75, 110, 0, 140, and 60, the following counts amounts of at least 100:

With those amounts in B2:B8, =COUNTIF(B2:B8,">=100") returns 3, because 120, 110, and 140 meet the condition.

If the threshold is stored in E2, join the operator to that cell with an ampersand. With the same amounts in B2:B8 and 100 in E2, =COUNTIF(B2:B8,">="&E2) also returns 3. The ampersand combines the comparison operator and the value into a criterion Excel can evaluate.

Basic SUMIF examples

Assume A2:A8 contains North, South, North, East, North, South, and North, while B2:B8 contains 120, 90, 75, 110, 0, 140, and 60. To add only the sales for North, enter:

With regions in A2:A8 and their sales in B2:B8, =SUMIF(A2:A8,"North",B2:B8) returns 255: 120 + 75 + 0 + 60. The first range is checked for North; the third argument identifies which corresponding values to add.

A cell reference makes the region selectable. With E2 containing North and the same data in A2:B8, =SUMIF(A2:A8,E2,B2:B8) returns 255. This is helpful in a report where a dropdown or typed choice changes the category being summarized.

SUMIF can also test a numeric range and add the qualifying numbers from that same range. With B2:B8 containing 120, 90, 75, 110, 0, 140, and 60, =SUMIF(B2:B8,">=100") returns 370, the sum of 120, 110, and 140. When no sum_range is supplied, the tested range is also the range being summed.

Text criteria, wildcards, and blanks

For exact text, enter the text in quotation marks in the formula, or refer to a cell containing it. Excel’s COUNTIF and SUMIF comparisons are case-insensitive. If capitalization matters, these functions alone do not distinguish “North” from “NORTH.”

Wildcards let a criterion match part of a text value. An asterisk (*) represents any number of characters, including none; a question mark (?) represents exactly one character. For example, if D2:D7 contains Notebook, Notebook stand, Pen, notebook, Binder, and a blank cell, the formula =COUNTIF(D2:D7,"*note*") returns 3. It finds the three entries containing “note,” regardless of capitalization. A criterion such as "P?n" would match “Pen” because the question mark stands for one character.

To search for a literal wildcard character rather than use it as a pattern, put a tilde (~) before it. For example, a criterion containing "~*" looks for an actual asterisk. This is useful when cells contain codes or labels that include wildcard symbols.

Blank and nonblank checks are also possible. A blank criterion is often written as an empty string, while a not-equal-to-empty criterion checks for nonblank cells. Be mindful that a formula returning an empty string may look blank but is not necessarily the same as a genuinely empty cell for every kind of test. If blank handling matters, test the formula on representative rows in your workbook.

Working with dates and changing thresholds

Excel stores dates as serial values, so date criteria should be built from actual date values rather than ambiguous text. A reliable approach is to combine a comparison operator with DATE, which constructs a date from year, month, and day. This avoids confusion between regional formats such as month/day/year and day/month/year.

Suppose A2:A5 contains actual Excel dates of January 5, January 15, February 1, and January 31, 2026, and B2:B5 contains 100, 200, 300, and 150. To sum the amounts from January 2026, use a start date inclusive and the first day of February exclusive:

With dates in A2:A5 and amounts in B2:B5, =SUMIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1)) returns 450: 100 + 200 + 150. The exclusive end date includes every time on January 31 if the date cells also contain times, while excluding February 1.

For a single date threshold, COUNTIF and SUMIF can use the same operator-and-cell pattern used for numbers. Store a real Excel date in a cell and join the comparison operator to that reference. Avoid typing a date criterion in a format that Excel might interpret differently under another locale.

When to use COUNTIFS and SUMIFS

COUNTIF and SUMIF each support one criterion. Their plural forms, COUNTIFS and SUMIFS, support multiple conditions and are the right choice when a question combines fields—for example, “count North orders that are Open” or “sum North sales assigned to Ana.” Each criteria range in these functions must align row by row with the others and, for SUMIFS, with the sum range.

Suppose A2:A7 contains North, South, North, East, North, and South, while B2:B7 contains Open, Open, Closed, Open, Open, and Closed. With regions in A2:A7 and statuses in B2:B7, =COUNTIFS(A2:A7,"North",B2:B7,"Open") returns 2. Both conditions must be true in a row for that row to count.

For a total with two conditions, suppose A2:A6 contains North, North, South, North, and South; B2:B6 contains Ana, Ben, Ana, Ana, and Ben; and C2:C6 contains 120, 80, 90, 60, and 140. With region in A2:A6, salesperson in B2:B6, and sales in C2:C6, =SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Ana") returns 180: the two North/Ana rows total 120 + 60.

Notice the argument order: SUMIF starts with the criteria range, but SUMIFS starts with the sum range. That difference is a common source of errors when switching between the two functions.

Choosing the right function and checking results

Choose based on the question being asked, not on how similar the function names look:

Use COUNTIF when one condition determines how many cells to count.
Use SUMIF when one condition determines which values to add.
Use COUNTIFS when multiple conditions determine the count.
Use SUMIFS when multiple conditions determine the total.

COUNTIF is not a substitute for SUMIF: counting qualifying rows does not add their values. Likewise, SUMIF does not count matches unless the values being summed happen to be 1s. If the goal is to count orders by region and total revenue by region, a report may need both functions.

Before relying on a result, compare it with a small manual check. Filter the source table to the same conditions, verify that the matching rows are the ones expected, and add a few values by hand. For a larger workbook, testing the formula against a small, clearly understood sample can reveal a wrong range or a mismatched criterion.

Common errors and practical fixes

A result of zero often means the criterion does not match the underlying data as expected. Check for leading or trailing spaces, inconsistent labels, numbers stored as text, and criteria that use the wrong comparison operator. A wildcard can help with inconsistent text patterns, but it should not conceal a data-cleaning problem when the labels are supposed to be standardized.

A SUMIF total that is too high or low may use a sum range that does not line up with the criteria range. For example, summing B2:B20 while checking A3:A21 shifts the relationship between categories and amounts. Keep ranges the same size and start and end them on corresponding rows. In multi-criteria formulas, check every criteria range as well.

Another common mistake is typing a comparison such as >=100 without quotation marks as the criterion. The operator and threshold need to be supplied as a criterion string, or joined to a cell reference. Also remember that COUNTIF and SUMIF ignore capitalization differences; they are not case-sensitive tools.

Excel for Microsoft 365 on Windows normally displays formulas with commas as argument separators in many regional settings. If a formula copied from this page is rejected, the computer’s regional list-separator setting may require semicolons instead. Mac and web users may also see separators determined by their locale. The function logic is the same, but use the separator Excel expects in that installation.

Frequently Asked Questions

Can COUNTIF count cells that contain a word?
Yes. Use an asterisk on each side of the word as a partial-match criterion, such as "*paid*". The asterisks allow characters before and after the word. COUNTIF is not case-sensitive.

Can SUMIF add values greater than a number in another cell?
Yes. Build the criterion by joining the comparison operator to the cell reference with an ampersand. The referenced cell should contain a number, and the operator belongs in quotation marks.

Why does SUMIF return zero when matching text looks identical?
Check for extra spaces, different punctuation, hidden characters, or numbers stored as text. Also verify that the formula checks the intended range and that its sum range corresponds to the same rows.

Can COUNTIF or SUMIF use more than one condition?
Not directly. Use COUNTIFS to count rows that meet multiple conditions, or SUMIFS to add values for rows meeting multiple conditions. Their criteria ranges must align.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF and SUMIF are case-insensitive, so capitalization alone does not make two text values different to these functions.

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.