COUNTIFS and SUMIFS: Multiple Conditions in Excel

To use Excel SUMIFS with multiple criteria, give it the numbers to add first, then one range/criteria pair for each condition. COUNTIFS works the same way without the sum range: it counts the rows that match every pair. This guide is for anyone building sales, HR or finance summaries, and it covers the syntax, comparisons, OR logic and the one-formula summary grid that often comes up in job tests.

What do COUNTIFS and SUMIFS do?#

COUNTIFS counts rows that meet all of your conditions, and SUMIFS adds a column of numbers for those rows. The syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Each condition is a pair: a range to check, then what it must match. You can add up to 127 pairs. Both functions have been in Excel since Excel 2007.

The examples use this order table in A1:E9:

How to count with multiple conditions using COUNTIFS#

Write one pair per condition, separated by commas. To count orders that are North and Laptop:

=COUNTIFS(A2:A9,"North",C2:C9,"Laptop")

The result is 2 (rows 2 and 7). Pair 1 is the Region column with "North". Pair 2 is the Product column with "Laptop". Row 4 is North but a Monitor, so it doesn't count.

Comparisons work in any pair. To count East orders with 4 or more units:

=COUNTIFS(A2:A9,"East",D2:D9,">=4")

That gives 2 (4 and 8 units). Use >= so an order of exactly 4 counts.

Criteria can also come from cells, so a manager can change the question without editing the formula. If K1 holds East and K2 holds 4:

=COUNTIFS(A2:A9,K1,D2:D9,">="&K2)

This gives the same 2. A plain cell reference needs no quotes. When you add an operator, keep the operator in quotes and join the cell with &; ">=K2" would look for the text K2.

How to use Excel SUMIFS with multiple criteria#

Put the range to add first, then the same pairs you would give COUNTIFS. To total North laptop revenue:

=SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop")

The result is 10,800 (3,600 + 7,200). Here is what each part does:

Watch the order. In SUMIF the sum range comes last, but in SUMIFS it comes first. Writing =SUMIFS(A2:A9,"North",C2:C9,"Laptop",E2:E9) is a very common SUMIFS mistake. SUMIF vs SUMIFS explains the difference in full.

One more example. Kofi's revenue from orders of 3 or more units:

=SUMIFS(E2:E9,B2:B9,"Kofi",D2:D9,">=3")

Only row 2 matches, so the result is 3,600.

How to count or sum between two values#

Use the same column twice, once with a lower limit and once with an upper limit. To count orders of 2 to 5 units, both included:

=COUNTIFS(D2:D9,">=2",D2:D9,"<=5")

The result is 5 (3, 5, 2, 4 and 2 units). This is also how you total between two dates. See how to sum by date range or month with SUMIFS.

How to use OR logic with SUMIFS#

Every pair in SUMIFS is an AND condition, so for OR you add separate results. To total North or East revenue:

=SUMIFS(E2:E9,A2:A9,"North")+SUMIFS(E2:E9,A2:A9,"East")

That gives 11,300 + 6,800 = 18,100. You can also write =SUM(SUMIFS(E2:E9,A2:A9,{"North","East"})). The curly brackets hold a list, SUMIFS returns one total per item, and SUM adds them.

Don't put both regions on the same column as two pairs. A2:A9,"North",A2:A9,"East" asks for rows that are North and East at once, so it returns 0.

How to build a summary grid with one formula#

A summary grid puts one category down the side and another across the top, with a SUMIFS in every cell. Put North, South and East in G8:G10, and Laptop and Monitor in H7:I7. In H8, type:

=SUMIFS($E$2:$E$9,$A$2:$A$9,$G8,$C$2:$C$9,H$7)

Fill right to I8 (Windows: Ctrl+R; Mac: ⌘R), then fill H8:I10 down (Windows: Ctrl+D; Mac: ⌘D).

The trick is the mixed references. $G8 always reads column G, but its row changes. H$7 always reads row 7, but its column changes. The data ranges are fully locked. You get this grid:

Check it: the six cells add up to 22,950, the same as =SUM(E2:E9). Swap SUMIFS for COUNTIFS($A$2:$A$9,$G8,$C$2:$C$9,H$7) to build a count grid instead.

Why is COUNTIFS or SUMIFS returning 0 or an error?#

Most problems come from mismatched ranges or criteria that don't match the data exactly:

1. #VALUE!: the ranges are different sizes, such as E2:E9 with A2:A10. Every range must cover the same rows.
2. 0 from a wrong order: in SUMIFS the sum range must be first.
3. 0 from a typo or space: "North " with a trailing space doesn't match "North". Clean the data with TRIM.
4. Criteria glued to a cell inside quotes: ">=G1" is text. Write ">="&G1.
5. Numbers stored as text: ">=4" skips units imported as text.

Where you'll use this at work#

Multi-condition totals show up in almost every office role: headcount by department and level, sales by region and product, hours by person and project. They are worth naming on a CV: how to list Excel skills on your resume shows how to describe them, and Excel skills for HR jobs covers the HR versions.

The same pairs also drive AVERAGEIFS for conditional averages and MAXIFS and MINIFS for the highest and lowest values. Partial matches like "starts with CAB-" use wildcards in your criteria. Quick references: the COUNTIFS guide and the SUMIFS guide.

Practise it#

Practise COUNTIFS and SUMIFS in a live spreadsheet with pairs, comparisons and cell-based criteria. Then take the summary-grid skill check, where you fill a region-by-product grid with one formula and prove the totals add up.