SUMIFS and COUNTIFS: how to sum and count with multiple conditions

Finding out how much a sales rep sold in total is easy. Things get harder when the question has several parts: how much did Ana sell in the North region, how many orders over 200 were there in March, or how many times does the same product appear for the same rep. That is what SUMIFS and COUNTIFS are for.

Both functions apply every condition you give them at once and only take into account the rows that meet all of them. You will see their syntax, examples with text, numbers, dates and wildcards, how to build a summary table and how to use them to find repeated records.

The sample data

We will use this sales log, with headers in row 1 and data in rows 2 to 9. Column A holds real dates, not text:

Syntax of both functions

Both have been available since Excel 2007 and also exist in Google Sheets and LibreOffice Calc.

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2)

• sum_range: the cells with the numbers to add up (only in SUMIFS).
• criteria_range: the column where each condition is checked.
• criteria: the condition, for example "Ana", ">=200" or a cell reference.

You can add up to 127 range and criteria pairs. Watch out for a detail that trips up many people: in SUMIF the range to sum goes last, while in SUMIFS it comes first.

Summing and counting with several conditions

To add up Ana’s sales in the North region:

=SUMIFS(E2:E9,B2:B9,"Ana",C2:C9,"North")

The result is 520: only rows 2 and 4 count. Ana’s sale in the South region is left out. To count how many Product A sales there were in the North region:

=COUNTIFS(C2:C9,"North",D2:D9,"Product A")

It returns 3 (rows 2, 5 and 9).

Criteria with operators

To compare numbers, write the operator and the value together inside quotes. This formula counts North region sales of 200 or more:

=COUNTIFS(E2:E9,">=200",C2:C9,"North")

The result is 4. Valid operators are =, <>, >, <, >= and <=. For example, "<>South" counts everything that is not South.

Criteria with dates

To add up a period, put the start date in K1 and the end date in K2, and join the operator to the cell with &:

=SUMIFS(E2:E9,A2:A9,">="&K1,A2:A9,"<="&K2)

With March 1 and March 31 in those cells, the result is 1670. Notice that the same range appears twice, once for each limit. Using cells instead of typing the date inside the formula avoids problems with each country’s date format.

Wildcards

In text criteria you can use * (any number of characters) and ? (a single character). To count the sales of reps whose name starts with M:

=COUNTIFS(B2:B9,"M*")

It returns 2. If you need to search for an actual asterisk, type it as ~*.

Building a summary table

The most useful application is building a small cross-tab report. Type the reps in G2:G4, the regions in H1 (North) and I1 (South), and this formula in H2:

=SUMIFS($E$2:$E$9,$B$2:$B$9,$G2,$C$2:$C$9,H$1)

Then copy it to the rest of the table. The $ signs are the key:

1. The data ranges have $ on both row and column, so they do not shift when copied.
2. $G2 locks the column: each row of the table always reads its own rep.
3. H$1 locks the row: each column always reads its own region.
4. Press F4 several times on a reference to cycle through the anchoring types.

The sum of the whole table (2110) matches the total of column E, a quick check that no row has been left out.

When you need an OR condition

All the conditions in these functions are combined with AND: a row must meet every one of them. If you want to add up the sales of Ana or Luis, the clearest way is to add two formulas together:

=SUMIFS(E2:E9,B2:B9,"Ana")+SUMIFS(E2:E9,B2:B9,"Luis")

The result is 1520 (1020 from Ana plus 500 from Luis). Just make sure the conditions do not overlap; if a row could meet both, it would be counted twice.

Spotting duplicate records with COUNTIFS

COUNTIFS also tells you how many times a combination of values appears. In a helper column, type this in row 2:

=IF(COUNTIFS($B$2:$B$9,B2,$D$2:$D$9,D2)>1,"Repeated","")

The formula counts how many rows have the same rep and the same product as the current row. In the sample data it flags the pairs Ana and Product A (rows 2 and 7) and Luis and Product A (rows 6 and 9). It is handy for reviewing before deleting: you can filter the helper column and decide what to do with each case.

If you simply want to remove the repeated rows from a large file, our free tool to remove duplicates does it with XLSX, XLS, CSV and ODS files, no formulas needed.

Common mistakes

• Ranges of different sizes. If the sum range is E2:E9 and a criteria range is B2:B8, Excel returns #VALUE!. All of them must have the same number of rows.
• Operator not concatenated. ">=K1" compares against the text K1, not the cell’s value. The correct form is ">="&K1.
• Numbers stored as text in the sum range. They are ignored, so the total comes out lower than expected. This often happens with data pasted from other programs or imported from CSV.
• Closed external workbooks. If the ranges are in another file and that file is closed, the formula returns #VALUE!. Open it or copy the data into the same workbook.
• Extra spaces. “Ana ” with a trailing space does not match “Ana”. Clean the column with TRIM before calculating.

Conclusion

SUMIFS and COUNTIFS answer questions with several conditions without PivotTables or manual filters. With criteria in cells, mixed references and the & operator you can build reports that update themselves when you add data.

Keep the ranges the same size, check totals against an overall sum and clean the data before you start: most odd results come from spaces, text values or repeated rows.

Frequently asked questions