SUMIFS with Multiple Criteria

All versionsSUMIFSAND logic

SUMIFS adds up numbers only on the rows where every condition you give it is true. It is the workhorse for answering questions like “total West-region Widget sales” — add one criteria pair per condition and they combine with AND logic.

Quick formula: to total column D where region (B) is West and product (C) is Widget:

=SUMIFS(D2:D8, B2:B8, "West", C2:C8, "Widget")

The first argument is the range to add; every pair after it is a range, criteria condition that must hold.

Functions used (tap for the full reference guide):

SUMIFS SUMPRODUCT

The example

A sales log. We want total West / Widget sales.

	A	B	C	D
1	Rep	Region	Product	Amount
2	Ana	West	Widget	$120
3	Ben	East	Widget	$90
4	Cy	West	Gadget	$60
5	Dot	West	Widget	$200
6	Eve	South	Widget	$75
7	Fin	West	Gadget	$40
8	West / Widget total:			$320

The formula

The total is:

=SUMIFS(D2:D7, B2:B7, "West", C2:C7, "Widget") // 120 + 200 = 320

How it works

Read each pair as a filter that must pass:

The sum range D2:D7 is what gets added — the amounts.
Pair 1, B2:B7, "West", keeps only West rows.
Pair 2, C2:C7, "Widget", further keeps only Widget rows. Both must be true.
Only Ana (120) and Dot (200) pass both filters → $320.

Order matters in SUMIFS, not SUMIF. In SUMIFS the sum range comes first. In the older SUMIF it comes last. Mixing them up is the #1 cause of wrong totals.

Try it: interactive demo

Live demo

Pick a region and product; the SUMIFS formula and total update against the log above.

Region

Product

Total:

Variations

Greater-than / date conditions

Criteria can be comparisons. West Widget sales over $100:

=SUMIFS(D2:D7, B2:B7, "West", C2:C7, "Widget", D2:D7, ">100")

Criteria from a cell

Point criteria at input cells so the report is interactive:

=SUMIFS(D2:D7, B2:B7, G1, C2:C7, G2)

OR logic (West OR East)

SUMIFS is AND-only. For OR, add two SUMIFS or use SUMPRODUCT:

=SUM(SUMIFS(D2:D7, B2:B7, {"West","East"}, C2:C7, "Widget"))

Pitfalls & errors

Wrong total / $0. The sum range and every criteria range must be the same size and shape. D2:D7 with B2:B8 returns #VALUE! or a wrong figure.

SUMIF vs SUMIFS argument order. SUMIFS: sum range first. SUMIF: sum range last. They are not interchangeable.

Text vs numbers. A criterion of "100" (text) won’t match a real number 100 reliably; use ">="&100 style comparisons or ensure consistent types.

Practice workbook

📊

Download the free SUMIFS with Multiple Criteria practice workbook

The sales log with live SUMIFS for region+product, comparison criteria, and the OR pattern, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I use SUMIFS with two or more criteria?

Put the sum range first, then add a range/criteria pair for each condition: =SUMIFS(D2:D7, B2:B7, "West", C2:C7, "Widget"). All conditions must be true (AND logic) for a row to be added.

What's the difference between SUMIF and SUMIFS?

SUMIF takes one condition and the sum range comes last. SUMIFS takes many conditions and the sum range comes first. Use SUMIFS for one or more criteria to keep argument order consistent.

How do I do OR logic with SUMIFS?

SUMIFS only does AND. For OR, add multiple SUMIFS together, or pass an array constant like {"West","East"} and wrap in SUM, or switch to SUMPRODUCT.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SUMIFS · SUMPRODUCT