Excel SUMIF and SUMIFS Functions

last modified October 5, 2026

The SUMIF and SUMIFS functions add only those cells that satisfy one or more conditions. SUMIF applies a single criterion, while SUMIFS applies several criteria at once using AND logic. This tutorial covers their syntax, the difference in argument order, criteria with comparison operators, wildcards, and dates, the rules Excel uses when the sum range does not line up with the criteria range, and the errors that appear when the arguments are malformed.

SUMIF/SUMIFS Function Basics

SUMIF adds the values that meet a single criterion, while SUMIFS adds the values that meet every one of several criteria (AND logic). Both functions are essential for data analysis when you need to sum specific subsets of data.

Function	Description	Syntax
SUMIF	Sums cells that meet one condition	=SUMIF(range, criteria, [sum_range])
SUMIFS	Sums cells that meet all conditions	=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

This table compares both functions. Note the different argument order between them. SUMIFS takes the sum range first and then pairs of criteria ranges and criteria, while SUMIF puts the criteria range first and the sum range last, where it is optional.

Argument	Meaning
range / criteria_range	The cells tested against the criteria. SUMIF has one range; SUMIFS takes one criteria range per condition and accepts up to 127 range/criteria pairs. Every criteria range must have the same dimensions as the sum range.
criteria	The condition to satisfy. It can be a number (100), text ("East"), a comparison (">100"), a wildcard pattern ("Pro*"), or a reference joined to an operator (">"&D1). Text criteria are not case-sensitive and must be quoted; numeric criteria do not need quotes.
sum_range	The cells that are actually added. In SUMIF it is optional and comes last; in SUMIFS it is required and comes first. When omitted, SUMIF adds the criteria range itself.

If no cell satisfies the criteria, both functions return 0, not an error. Blank and text cells in the sum range contribute nothing to the total.

Basic SUMIF Example

This example demonstrates the simplest use of SUMIF to sum sales for a specific salesperson.

A (Salesperson)	B (Amount)	C
John	100
Mary	150
John	200
		=SUMIF(A1:A3, "John", B1:B3)

=SUMIF(A1:A3, "John", B1:B3)

This formula checks A1:A3 for "John" and adds the corresponding values in B1:B3. The result is 300 (100+200). The third argument names the cells to add when the condition is met. Text matching is not case-sensitive, so the criteria "JOHN" or "john" would produce the same total.

SUMIF with Comparison Operator

SUMIF supports the comparison operators >, <, >=, <=, and <>. This example sums values above a threshold without a sum range, so Excel adds the cells of the criteria range itself.

A (Values)	B
50
75
100
	=SUMIF(A1:A3, ">80")

=SUMIF(A1:A3, ">80")

This formula adds the values in A1:A3 that are greater than 80. The result is 100, the only value above the threshold. Note we omitted the third argument since the range we are testing is also the range we are adding. The operator and the value are written together inside one quoted string. To compare against a cell instead of a literal, join the operator to the reference with the & character: =SUMIF(A1:A3, ">"&D1). If D1 contains 80, this produces the same result as the formula above.

SUMIF with Wildcards

SUMIF supports wildcards for partial text matching. The asterisk (*) matches any sequence of characters, while the question mark (?) matches any single character. Wildcards work only with text criteria, not with numbers.

A (Product)	B (Sales)	C
Product A	200
Service B	150
Product C	300
		=SUMIF(A1:A3, "Pro*", B1:B3)

=SUMIF(A1:A3, "Pro*", B1:B3)

This formula sums sales in B1:B3 where the product name in A1:A3 starts with "Pro". It matches "Product A" and "Product C", so the result is 500 (200+300). The pattern "Pro?" would match a product name of exactly four characters starting with "Pro". To match an actual asterisk or question mark, precede it with a tilde (~), for example "Pro~*".

SUMIF with Dates

Dates in Excel are stored as serial numbers, so they can be compared like any other number. This example sums sales that occurred after a given day.

A (Date)	B (Sales)
1/1/2025	500
1/15/2025	600
2/1/2025	700
	=SUMIF(A1:A3, ">"&DATE(2025,1,15), B1:B3)

=SUMIF(A1:A3, ">"&DATE(2025,1,15), B1:B3)

This formula sums sales dated after January 15, 2025. The result is 700: the first row falls on the boundary and is excluded, and only the February row remains. Building the criterion with the DATE function returns a real date serial, which is safer than typing a date inside the criteria string like ">1/15/2025", because the literal is interpreted according to the regional date format while DATE is not.

Basic SUMIFS Example

SUMIFS extends SUMIF by supporting multiple criteria. This example sums sales for a specific region and product.

A (Region)	B (Product)	C (Sales)	D
East	Widget	100
West	Gadget	150
East	Gadget	200
			=SUMIFS(C1:C3, A1:A3, "East", B1:B3, "Gadget")

=SUMIFS(C1:C3, A1:A3, "East", B1:B3, "Gadget")

This formula sums C1:C3 where A1:A3 is "East" AND B1:B3 is "Gadget". Only the third row satisfies both conditions, so the result is 200. Note the sum range comes first in SUMIFS, unlike SUMIF, and each condition is written as its own criteria range/criteria pair.

SUMIFS with a Date Range

SUMIFS can bound a value between two limits by testing the same range twice. This example sums sales made in January 2025.

A (Date)	B (Sales)	C
1/1/2025	500
1/15/2025	600
2/1/2025	700
		=SUMIFS(B1:B3, A1:A3, ">="&DATE(2025,1,1), A1:A3, "<="&DATE(2025,1,31))

=SUMIFS(B1:B3, A1:A3, ">="&DATE(2025,1,1), A1:A3, "<="&DATE(2025,1,31))

This formula adds B1:B3 where the date in A1:A3 is on or after 1/1/2025 and on or before 1/31/2025. The result is 1100 (500+600); the February row fails the upper limit. Reusing A1:A3 in two pairs is allowed because both pairs test the same rows. A quoted literal such as ">=1/1/2025" also works in many locales, but the DATE form is not affected by the regional date format.

SUMIFS with Multiple Criteria Types

This advanced example combines different criteria types (text, number, date) in SUMIFS to calculate a total meeting complex conditions.

A (Region)	B (Sales)	C (Date)	D
East	500	1/1/2023
West	300	2/1/2023
East	700	3/1/2023
			=SUMIFS(B1:B3, A1:A3, "East", B1:B3, ">400", C1:C3, ">"&DATE(2023,1,15))

=SUMIFS(B1:B3, A1:A3, "East", B1:B3, ">400", C1:C3, ">"&DATE(2023,1,15))

This formula sums sales where: region is "East", sales > 400, and date is after Jan 15, 2023. The result is 700 (only the third row meets all criteria). Note the DATE function and concatenation with & for the date comparison.

SUMIFS with OR Logic

SUMIFS uses AND logic across its pairs. To get OR logic, add several SUMIFS results together. This example sums sales for either product.

A (Product)	B (Sales)	C
Widget	100
Gadget	150
Widget	200
		=SUMIFS(B1:B3, A1:A3, "Widget") + SUMIFS(B1:B3, A1:A3, "Gadget")

=SUMIFS(B1:B3, A1:A3, "Widget") + SUMIFS(B1:B3, A1:A3, "Gadget")

This formula sums Widget sales (100+200=300) plus Gadget sales (150), totaling 450. A single SUMIFS cannot express OR inside one pair, and the additive form double-counts rows that satisfy both conditions. When criteria can overlap, use SUMPRODUCT:

=SUMPRODUCT(((A1:A3="Widget")+(A1:A3="Gadget")>0)*B1:B3)

Each comparison returns TRUE or FALSE, which the addition turns into 1 and 0. The >0 comparison caps the combined flag at TRUE for any match, so a row matching both products would still be counted once. In this data no row does, and the result is the same 450.

How the Sum Range Is Sized

The two functions differ in how strictly they require the sum range to match the criteria range.

For SUMIF, if the sum range is a different size, Excel uses its top-left cell as the anchor and resizes it to match the shape of the criteria range. In the table below the formula is written with B1:B2, yet Excel treats it as B1:B3, so the result is 250.

A (Region)	B (Sales)
East	100
West	200
East	150
	=SUMIF(A1:A3, "East", B1:B2)

=SUMIF(A1:A3, "East", B1:B2)

The short B1:B2 reference is stretched downward to cover the same three rows as A1:A3, so the second "East" row (150) is included and the total is 250. Relying on this behavior makes formulas hard to read and can slow recalculation on large ranges, so keep both ranges the same size.

For SUMIFS this shortcut does not apply. The sum range and every criteria range must have exactly the same number of rows and columns, otherwise the formula returns #VALUE!.

=SUMIFS(B1:B2, A1:A3, "East")

Because B1:B2 and A1:A3 have different shapes, this formula returns #VALUE! instead of a sum. Always build the criteria ranges and the sum range over the same rows.

SUMIF, SUMIFS, and SUMPRODUCT

The three approaches overlap, and each has a natural role:

Aspect	SUMIF	SUMIFS	SUMPRODUCT
Criteria	One condition	1 to 127 pairs, AND logic	Unlimited, AND or OR logic
Case-sensitive matching	No	No	Yes, with EXACT
Range size rules	Sum range is resized from its top-left cell	All ranges must match exactly	Arrays must match or #VALUE!
Result when nothing matches	0	0	0

Use SUMIF for quick single-condition totals, SUMIFS for everyday multi-criteria totals, and SUMPRODUCT when criteria must be combined with OR logic or compared case-sensitively, for example =SUMPRODUCT(EXACT(A1:A3,"John")*B1:B3). SUMIFS is generally faster than SUMPRODUCT on large ranges, so prefer it whenever AND logic is enough.

SUMIF/SUMIFS Errors and Troubleshooting

Cause	Symptom	Fix
A text or comparison criterion is not quoted	0 instead of the expected sum	Enclose text and comparison criteria in quotes, for example "East" or ">400"
The operator and value are split into separate arguments	#VALUE! error or no match	Write the operator and value together in one quoted string, for example ">400"
A cell reference is not joined to its operator	Wrong result or no match	Concatenate with &, for example ">"&D1
The SUMIFS ranges have different sizes	#VALUE! error	Make the sum range and every criteria range the same number of rows and columns
A SUMIF sum range has the wrong shape	Unexpected total	Remember it is resized from its top-left cell to match the criteria range
Wildcards are used on numeric data	No match	Wildcards match text only; compare numbers with operators instead
Dates are compared as text literals	Wrong or empty result	Build the criteria with a date serial, for example ">"&DATE(2023,1,15)
The criteria string is longer than 255 characters	An incorrect result	Split the string and join the parts with &, or use SUMPRODUCT
The sum range contains TRUE or FALSE values	An unexpected total	TRUE is added as 1 and FALSE as 0; convert the values to numbers first

The SUMIF and SUMIFS functions make it easy to total a subset of data without sorting or filtering it first. Remember the reversed argument order: the sum range is last and optional in SUMIF, but first and required in SUMIFS. Quote comparison criteria, join operators to cell references with &, build date criteria with DATE, and keep every SUMIFS range the same size. Both functions return 0 when nothing matches and ignore blanks and text in the sum range, and SUMPRODUCT is available when you need OR logic or case-sensitive totals. Mastering these functions will significantly enhance your data analysis capabilities in Excel.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.