SUMIFS examples: how to sum with multiple criteria in Excel

The syntax

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

• sum_range comes first: the numbers to add.
• Then pairs of criteria_range and criteria: where to look and what to look for.
• A row is included only if all conditions are true (AND logic).
• Every range must have the same size as sum_range.

Text criteria go in quotes ("North"). A plain number does not need them. A comparison is written as text, operator first: ">1000".

Worked example: one invoice list, six formulas

Rows 2 to 9 are the data. Each formula in column E answers one question about it.

Worked example: one invoice list, six formulas

	A	B	C	D	E
1	Date	Customer	Region	Status	Amount
2	14 Feb 2026	Alder Co	North	Paid	1,250.00
3	27 Feb 2026	Birch Ltd	South	Open	830.00
4	03 Mar 2026	Alder Co	North	Paid	2,400.00
5	09 Mar 2026	Cedar Inc	North	Open	640.00
6	15 Mar 2026	Birch Ltd	South	Paid	1,780.00
7	21 Mar 2026	Dune LLC	West	Paid	960.00
8	28 Mar 2026	Cedar Inc	North	Paid	1,120.00
9	02 Apr 2026	Alder Co	North	Open	1,500.00
10
11	North and Paid				4,770.00
12	Paid in March 2026 (date range)				6,260.00
13	Invoices over 1,000 (any status)				8,050.00
14	Customers starting with "A" (wildcard)				5,150.00
15	Every region except North				3,570.00
16	Number of open invoices in North (COUNTIFS)				2

Two criteria: region and status

=SUMIFS(E2:E9,C2:C9,"North",D2:D9,"Paid") adds the amounts in rows where the region is North and the status is Paid: 1,250 + 2,400 + 1,120 = 4,770.00. Text matching is not case-sensitive, so "north" works too. In a real sheet, put the criteria in cells (for example H2 for the region) and write C2:C9,H2. Then one formula, filled down a list of regions, builds a whole summary table.

A date range

To sum one month, use the date column twice: once for the start, once for the end.

=SUMIFS(E2:E9,D2:D9,"Paid",A2:A9,">="&DATE(2026,3,1),A2:A9,"<"&DATE(2026,4,1))

The operator is text and the date is a number, so they are joined with &. Using "less than the first day of the next month" instead of "less than or equal to the 31st" works for every month, and it still works if the dates include a time of day. The result is 6,260.00. With the start date in a cell, write ">="&H3.

Greater than, wildcards and "not equal"

• =SUMIFS(E2:E9,E2:E9,">1000") sums amounts above 1,000. The sum range can also be a criteria range: 8,050.00.
• =SUMIFS(E2:E9,B2:B9,"A*") uses a wildcard. * stands for any number of characters and ? for exactly one, so this matches every customer starting with A: 5,150.00.
• =SUMIFS(E2:E9,C2:C9,"<>North") excludes a value: 3,570.00.

COUNTIFS and AVERAGEIFS

The same criteria pairs work in COUNTIFS, which counts rows instead of adding them, and in AVERAGEIFS. There is no sum range in COUNTIFS, so the formula starts directly with the first criteria range: =COUNTIFS(C2:C9,"North",D2:D9,"Open") returns 2. AVERAGEIFS has the same argument order as SUMIFS. Divide a SUMIFS by a COUNTIFS with the same criteria and you get the same result as AVERAGEIFS.

OR logic: one condition or another

SUMIFS only does AND. For "North or South", add two SUMIFS: =SUMIFS(E2:E9,C2:C9,"North")+SUMIFS(E2:E9,C2:C9,"South"). With many alternatives, an array constant is shorter: =SUM(SUMIFS(E2:E9,C2:C9,{"North","South"})). Do not put both conditions on the same range in one SUMIFS: a region cannot be North and South at once, so the result is always 0.

Building a summary table

The real power of SUMIFS shows when the criteria come from cells. Put the regions down a column (H2:H4: North, South, West) and the statuses across a row (I1:J1: Paid, Open). One formula in I2 fills the whole grid:

=SUMIFS($E$2:$E$9,$C$2:$C$9,$H2,$D$2:$D$9,I$1)

The data ranges are fully locked with $, the region reference locks only the column ($H2) and the status reference only the row (I$1). Copy it across and down and every cell picks up its own region and status. Add a total row with SUM and check that it equals the plain total of the amount column, so you know no rows fall through the criteria.

SUMIF or SUMIFS?

SUMIF is the older single-condition version, and its arguments are in a different order: =SUMIF(range, criteria, [sum_range]), with the sum range last. SUMIFS works for one condition too, so many people use only SUMIFS and never have to remember two argument orders.

Common mistakes

• Ranges of different sizes. E2:E9 with C2:C10 returns #VALUE!. Use the same rows everywhere, or an Excel Table with structured references.
• Numbers stored as text. Amounts imported as text are ignored by SUMIFS, and the total is too low or 0. Convert them to numbers first.
• Hidden spaces. "North " with a trailing space does not match "North". Clean the data with TRIM.
• Operators outside the quotes. >"1000" is an error. Write ">1000", or ">"&H1 with a cell.
• Mixing up SUMIF and SUMIFS argument order. SUMIF has the sum range last, SUMIFS first.
• Relative ranges when filling down. Lock the data ranges with $ ($E$2:$E$9) before you copy the formula to a summary table.