How to use SUMIFS and COUNTIFS with multiple criteria in Excel

Spreadsheet tools · Published September 27, 2026

Total and count rows that match several conditions, such as a region and a date range, with worked examples and fixes for results that come out as zero.

The short answer

Use SUMIFS to add up the rows that meet every condition you give it, and COUNTIFS to count them. Put the range to add first, then pairs of "where to look" and "what to look for":

=SUMIFS(D2:D9,B2:B9,"East",C2:C9,"Acme")

That reads: add the amounts in D2:D9 where the region in B is East and the customer in C is Acme. With the sample data below, the answer is 390. Both functions work in every current version of Excel, including Excel for the web.

The sample data

All examples use this sheet. Column A holds real dates, and G1:G3 hold the criteria so you can change them without editing the formula.

SUMIFS step by step

1. Click the cell for the result (G4 here) and type =SUMIFS(.
2. Select the sum_range, the numbers to add: D2:D9.
3. Add the first criteria_range and criteria: B2:B9 and G1 (the region).
4. Add more pairs for each extra condition, close the bracket and press Enter.

Worked example: sales by region and month

To total East sales in February, test the date column twice, once for each end:

=SUMIFS(D2:D9,B2:B9,G1,A2:A9,">="&G2,A2:A9,"<="&G3)

The criteria ">="&G2 joins the operator to the date in G2, giving "on or after February 1". The operator must be in quotes and the cell reference outside them; ">=G2" would look for the text G2. Only two rows match (95 on 02/03 and 60 on 02/18), so the result is 155.

Without the helper cells, build the dates with DATE. This totals every region in February, using "before March 1" so it works whatever the month length. The result is 495:

=SUMIFS(D2:D9,A2:A9,">="&DATE(2026,2,1),A2:A9,"<"&DATE(2026,3,1))

SUMIF vs SUMIFS: the argument order

The older SUMIF handles one condition and puts the range to add last. SUMIFS puts it first. Both of these return 590:

=SUMIF(B2:B9,"East",D2:D9)

=SUMIFS(D2:D9,B2:B9,"East")

Mixing the two orders up is a common reason for a wrong total. SUMIFS works with one condition too, so you can use it everywhere.

COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS

COUNTIFS has no sum range: it only takes criteria pairs, and counts the rows where all of them are true. AVERAGEIFS, MAXIFS and MINIFS take the range to average, or find the largest or smallest value in, first, just like SUMIFS.

• =AVERAGEIFS(D2:D9,B2:B9,"West") returns 140. If no row matches, it returns #DIV/0!.
• =MAXIFS(D2:D9,B2:B9,"East") returns 175 and =MINIFS(D2:D9,B2:B9,"East") returns 60. If no row matches, they return 0.

Worked example: orders over $100 for one customer

=COUNTIFS(A2:A8,E1,B2:B8,">"&E2)

Acme has five orders, and three of them (120, 210 and 175) are over 100. The order of exactly 100 isn't counted, because ">" means strictly greater. Use ">="&E2 to include it, which gives 4. To total those orders instead of counting them, =SUMIFS(B2:B8,A2:A8,E1,B2:B8,">"&E2) returns 505.

Criteria you can use

• Numbers and operators: ">100", "<=50", "<>0". Always in quotes.
• Not equal: "<>East", or "<>"&G1 with a cell. On the Sales sheet both return 420.
• Wildcards: * matches any number of characters and ? exactly one. "B*" matches Birch (total 350), and "?cme" matches Acme. Put ~ before a real * or ?.
• Text matches regardless of case, so "east" finds East.

"Or" conditions

Every condition in SUMIFS must be true at once. For "Acme or Birch", add two SUMIFS together:

=SUMIFS(D2:D9,C2:C9,"Acme")+SUMIFS(D2:D9,C2:C9,"Birch")

That returns 950 (600 for Acme plus 350 for Birch). Adding works here because one row can't be both Acme and Birch. For conditions on different columns, such as "East or Acme", rows that match both would be counted twice: the two SUMIFS give 1,190, but the right answer is 800. Use SUMPRODUCT instead, which works in every version:

=SUMPRODUCT(((B2:B9="East")+(C2:C9="Acme")>0)*D2:D9)

Each comparison gives 1 for a match and 0 otherwise. Adding them and testing >0 marks rows that match either condition once, and multiplying by D2:D9 keeps only those amounts. The result is 800.

Why SUMIFS returns 0 (or the wrong total)

• Numbers stored as text. Amounts imported from other systems may be text. SUMIFS skips them without warning. Look for left-aligned numbers or green triangles, and convert them as described in how to clean up messy text.
• Extra spaces. "East " with a trailing space doesn't match "East". Clean the column with TRIM.
• Dates stored as text. ">=" comparisons only work on real dates. A text date usually sits on the left of the cell and doesn't change when you apply a date format.
• #VALUE!: the ranges aren't the same size, such as D2:D9 with B2:B10. Every range must cover the same rows.
• Operator inside the quotes with the cell: write ">"&E2, not ">E2".
• An empty criteria cell is treated as 0, which usually matches nothing.

If the data comes from a CSV file, Filecon's CSV to Excel in Smart mode stores plain numbers such as 95.50 as numbers and keeps IDs with leading zeros as text. It runs in your browser, and the file isn't uploaded. Values with currency signs or thousands separators, and dates, stay text on purpose, so convert those columns in Excel before using them in criteria.

In short

SUMIFS, AVERAGEIFS, MAXIFS and MINIFS take the result range first, then criteria pairs; COUNTIFS takes only the pairs. Join operators to cells with &, use two criteria on the same date column for a date range, and add formulas together for "or". When a result is 0, check for text numbers, spaces and text dates. To pull a single matching value rather than a total, see VLOOKUP and XLOOKUP, and Data › Filter is a quick way to check which rows a formula should include.