Excel SUMIF Function — How to Use It (with 6 Examples)

Contents

What does SUMIF do?
Syntax
6 real examples
SUMIFS — multiple conditions
Common mistakes
FAQ

What does SUMIF do?

SUMIF adds up only the numbers that meet a condition you set. It combines the logic of IF with the adding power of SUM — letting you total a subset of your data without filtering or sorting first.

Common uses: total sales for one region, sum expenses in one category, add up values above a threshold, or total orders for one customer. Any time you want a conditional total, SUMIF is the formula.

Syntax

=SUMIF(range, criteria, [sum_range])

Argument	Description
range required	The column to check the condition against — e.g. the Category column.
criteria required	The condition to match — e.g. "North", ">1000", or a cell reference.
sum_range optional	The column with the numbers to add. If omitted, SUMIF sums the range column itself.

6 real examples

Example 1

Sum sales for one product

=SUMIF(A2:A100, "Coffee", B2:B100)

Checks column A for "Coffee" and adds the corresponding values from column B.

Example 2

Sum values above a threshold

=SUMIF(B2:B100, ">1000")

Adds all values in B that exceed 1,000. When range and sum_range are the same column you can omit sum_range.

Example 3

Reference a cell for dynamic lookup

=SUMIF(A2:A100, E1, B2:B100)

E1 contains the product name. Change E1 and the sum updates automatically.

Example 4

Wildcard — sum anything containing a word

=SUMIF(A2:A100, "*coffee*", B2:B100)

The * wildcard matches any text before or after "coffee" — so "Iced Coffee", "Coffee Latte" and "Coffee" all match.

Example 5

Sum values NOT equal to something

=SUMIF(A2:A100, "<>Refund", B2:B100)

<> means "not equal to". This sums everything except refunds.

Example 6

Sum values above the average

=SUMIF(B2:B100, ">"&AVERAGE(B2:B100))

Combine a comparison operator in quotes with & and a formula to create a dynamic threshold.

SUMIFS — when you need multiple conditions

SUMIF handles one condition. For two or more conditions use SUMIFS — note the argument order changes: sum_range comes first.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

SUMIFS Example

Total Coffee sales in the North region only

=SUMIFS(C2:C100, A2:A100, "Coffee", B2:B100, "North")

💡 Key difference

In SUMIF, sum_range is the third argument. In SUMIFS, sum_range is the first argument. This trips up almost everyone the first time.

Common mistakes

⚠️ Mismatched range sizes

The range and sum_range must be the same size. A2:A100 and B2:B50 won't work correctly. Always use identical row counts.

⚠️ Forgetting quotes around text criteria

=SUMIF(A:A, Coffee, B:B) will error. Text criteria always need quotes: "Coffee". Number criteria and cell references do not need quotes.

⚠️ Extra spaces in data

"Coffee " (with a trailing space) won't match "Coffee". Use TRIM on your data to remove hidden spaces that cause missed matches.

FAQ

Is SUMIF case sensitive?

No. SUMIF treats "coffee", "Coffee", and "COFFEE" as identical. If you need case-sensitive summing, use SUMPRODUCT with EXACT.

Can SUMIF sum across multiple sheets?

Not directly. SUMIF works on one range at a time. For multi-sheet conditional sums, use SUMPRODUCT or sum individual SUMIF results from each sheet.

What is the difference between SUMIF and COUNTIF?

SUMIF adds the values that match your condition. COUNTIF counts how many cells match your condition. Both use the same syntax for their first two arguments.

Can I use SUMIF with dates?

Yes. Use comparison operators with dates: =SUMIF(A:A,">"&DATE(2026,1,1),B:B) sums values after 1 January 2026.

Practise SUMIF with real data

ExcelPro has SUMIF and SUMIFS exercises across every specialist track — realistic business data, instant feedback, free to start.

Try SUMIF exercises →

Related formulas

SUMIFS SUM COUNTIF AVERAGEIF IF SUMPRODUCT

The Excel SUMIF Functionexplained simply