SUMIFS in Excel: Count or Sum Matching Values?

Formulas & functions · Updated November 18, 2026

SUMIFS in Excel adds numeric values that meet one or more conditions; it does not normally count matching rows. To count rows instead, use COUNTIFS. With regions in A2:A7 and products in B2:B7, this formula counts rows that are both North and Widget: =COUNTIFS(A2:A7,"North",B2:B7,"Widget"). It returns 2 when those two combinations appear twice.

What SUMIFS and COUNTIFS Actually Do

The similar names can make these functions easy to confuse, but they answer different questions. SUMIFS returns a total from a specified sum range. COUNTIFS returns how many cells or rows meet all the specified criteria. In practical terms, ask whether you need a total amount or a number of matching records.

For example, a sales list might contain Region, Product, Units, and Revenue columns. “How many North Widget orders are there?” is a count question, so COUNTIFS is appropriate. “How many units did those orders contain?” is a sum question, so SUMIFS is appropriate. If the matching orders contain 3 and 1 units, the count is 2 orders while the units total is 4.

SUMIFS can produce a count-like result if the sum range contains a 1 for every record you want to count. That is a workaround, not its usual purpose. COUNTIFS communicates the intention directly and avoids relying on a helper column being populated correctly.

SUMIFS Syntax and Argument Order

The SUMIFS function has this structure: =SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...). The first argument is the range to add. Each following pair identifies a range to test and the condition it must satisfy. You can add more pairs when several conditions must apply.

For a sales table with Regions in A2:A7, Products in B2:B7, Units in C2:C7, and Revenue in D2:D7, the sum range could be the Units or Revenue column. If it is Units, SUMIFS adds the unit values from rows whose Region and Product meet the stated criteria. The criteria ranges should align with the sum range: use the same number of rows and corresponding records.

COUNTIFS has a related but simpler pattern: =COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...). There is no sum_range argument because the function counts cells meeting the conditions rather than adding values from a separate column.

In Microsoft 365 for Windows, formulas shown here use commas between arguments. Some regional settings use semicolons as the argument separator; if Excel rejects a formula copied from another locale, check the separator expected by your installation. Function names and the basic logic remain the same.

Count Matching Rows with COUNTIFS

Suppose the records are in rows 2 through 7, with Region in A, Product in B, Units in C, and Revenue in D. If you want to count North Widget records, use the two criteria ranges and their conditions. The formula returns 2 if rows 2 and 5 are the only rows with that combination.

With regions in A2:A7 and products in B2:B7, enter: =COUNTIFS(A2:A7,"North",B2:B7,"Widget").

COUNTIFS applies the criteria to corresponding rows. A row is counted only when its Region is North and its Product is Widget. This is an AND test: both conditions must be true on the same row. It does not count a North row plus a separate Widget row as one match.

For a more flexible worksheet, place the desired region in F2 and product in G2. With regions in A2:A7, products in B2:B7, and the criteria values in F2 and G2, use: =COUNTIFS(A2:A7,F2,B2:B7,G2). Changing either input cell updates the answer without editing the formula. This pattern is useful for summary sheets and reports where a reader chooses criteria from cells.

Sum the Values in Matching Rows

Use SUMIFS when the question is about a total. With Regions in A2:A7, Products in B2:B7, and Units in C2:C7, the following formula adds units only for rows that match North and Widget. If those rows contain 3 and 1 units, the answer is 4.

With the stated layout, enter: =SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Widget").

To add Revenue for the same records, keep both criteria ranges and replace the sum range with D2:D7. If the matching revenues are 60 and 20, the result is 80. With Regions in A2:A7, Products in B2:B7, and Revenue in D2:D7, use: =SUMIFS(D2:D7,A2:A7,"North",B2:B7,"Widget").

One way to make SUMIFS return a record count is to create a helper column containing the number 1 for each data row, then sum that column using the same criteria. For example, with a 1 in every row of E2:E7, Regions in A2:A7, and Products in B2:B7, this returns 2 for North Widget records: =SUMIFS(E2:E7,A2:A7,"North",B2:B7,"Widget"). This can be useful when the same criteria logic is already used for totals, but COUNTIFS is generally clearer for a simple count.

Use Criteria for Text, Numbers, and Dates

Criteria can be typed directly into a formula or supplied through a cell reference. Text criteria such as “North” match that text. Numeric criteria can identify exact values or use comparison operators. For example, a condition such as “>10” means greater than 10, while “>=10” includes 10.

When an operator is combined with a value stored in a cell, join them with the ampersand. With Units in C2:C7 and a threshold in F2, this counts unit values greater than the threshold: =COUNTIFS(C2:C7,">"&F2). If F2 contains 2, the formula counts values strictly above 2, not values equal to 2.

Dates are stored as numbers in Excel, so comparison criteria can also be used for date ranges. For instance, one criterion can be greater than or equal to a start date, and another can be less than an end date. Using an exclusive end date is often a straightforward way to include every time on the final day when the cells contain date-and-time values. Make sure the criteria cells contain real Excel dates rather than text that merely looks like a date.

For text patterns, Excel criteria support wildcards: an asterisk matches any sequence of characters, and a question mark matches one character. For example, “Nor*” can match North or Northeast. A tilde escapes a wildcard when you need to search for a literal asterisk or question mark. Exact criteria are usually preferable when your category names are controlled and consistent.

Multiple Criteria, OR Conditions, and Unique Values

Multiple criteria pairs in COUNTIFS or SUMIFS normally work as AND conditions. A record must satisfy every pair to be counted or included in the sum. That is exactly what is needed for queries such as “North region and Widget product.” It is also why a formula can return zero when one of several conditions does not match, even if each condition appears elsewhere in the data.

For an OR question, such as counting North records or South records, you may add separate COUNTIFS results. With Regions in A2:A7, the formula is: =COUNTIFS(A2:A7,"North")+COUNTIFS(A2:A7,"South"). This returns the total records in either region when each row can belong to only one of those categories. If the alternatives can overlap, adding separate counts may count a row more than once; the logic needs to account for that overlap.

COUNTIFS counts qualifying cells or records, not distinct labels. If a customer appears five times and meets the criteria on all five rows, a row count includes five. A request for the number of unique customers is a different problem: it requires deduplicating or using a distinct-count approach, rather than simply substituting COUNTIFS for SUMIFS.

Common Reasons a Formula Returns the Wrong Result

A result of zero or an unexpected total often points to a mismatch in the data or ranges rather than a problem with the function itself. Check these issues before rebuilding the formula:

  • Range sizes do not align. Use criteria ranges that correspond to the same rows as the sum range. Keep the start and end rows consistent.
  • Text is not identical. Trailing spaces, inconsistent spelling, or a different category label can prevent an expected match. Cleaning or standardizing source data is better than adding increasingly complicated criteria.
  • Numbers are stored as text. A value that looks numeric may not behave like a number in a sum or numeric comparison. Check the source cells and convert them where appropriate.
  • The wrong function answers the question. SUMIFS adds matching numeric cells; it does not report the number of matching records. COUNTIFS is the direct choice for a count.
  • Criteria use the wrong operator. A condition written as “>10” excludes 10. Use “>=10” if the boundary should be included.
  • Criteria ranges include different records. Starting one range on row 2 and another on row 3 can compare unrelated entries and produce a misleading answer.

When diagnosing a formula, test one criterion first, then add the next criterion. If the single-condition count is correct but the two-condition count is not, inspect the second field and verify that the two conditions are intended to apply to the same records.

Choosing Between SUMIFS, COUNTIFS, and Other Options

Choose COUNTIFS when the desired result is a number of matching rows and the logic involves one or more conditions. Choose SUMIFS when you want to add a numeric measure—such as units, hours, or revenue—for rows that meet conditions. Use SUMIF or COUNTIF when there is only one criterion; these are the single-condition counterparts.

A PivotTable can be a better fit when you need to explore many categories, compare several totals, or summarize a large table interactively. For a repeatable calculation in a worksheet cell, COUNTIFS or SUMIFS is often simpler. If you need to count distinct items rather than rows, first clarify what “unique” means in the dataset and use a distinct-count method rather than treating a standard conditional count as a unique count.

For maintainable formulas, convert a data range to an Excel table with Insert > Table, then use descriptive column names in structured references. Tables expand as new records are added, which can prevent a fixed range such as A2:A7 from missing later rows. In Microsoft 365 for Windows, the Insert tab contains the Table command; the same table feature is available in current Excel for Mac, though ribbon placement can vary with window size and interface updates.

Frequently Asked Questions

Can SUMIFS count cells that match criteria?
Not directly: SUMIFS adds values in its sum range. It can imitate a count if that range contains 1 for every record, but COUNTIFS is the clearer function for counting matching rows.

What is the Excel formula to count multiple conditions?
Use COUNTIFS with a range-and-criterion pair for each condition. For example, with Region in A2:A7 and Product in B2:B7, =COUNTIFS(A2:A7,"North",B2:B7,"Widget") counts rows where both conditions are true.

Does COUNTIFS count duplicates?
Yes. It counts each qualifying cell or row, including repeated names or values. It does not automatically reduce duplicates to one unique item.

Can SUMIFS use more than one condition?
Yes. Add further criteria-range and criteria pairs after the sum range. The function adds a value only when all supplied conditions are satisfied on the corresponding row.

Why does COUNTIFS return zero when the value is visible?
Check for extra spaces, spelling differences, text-versus-number mismatches, and criteria ranges that do not cover the intended rows. Also confirm that every condition is meant to be true on the same row.

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.