What is SUMIF function in Excel?

SUMIF adds numbers that meet a specific condition. Syntax: =SUMIF(range, criteria, [sum_range]). Example: =SUMIF(A2:A10,">100",B2:B10) sums values in B2:B10 where A2:A10 is greater than 100. Use for conditional summing with one criterion.

What is the difference between SUM and SUMIF?

SUM adds all numbers: =SUM(A1:A10). SUMIF adds only numbers meeting criteria: =SUMIF(A1:A10,">50"). SUMIF is conditional, SUM is not. Use SUM for everything, SUMIF when you need to filter first.

How do I use SUMIF with text criteria?

Use quotes around text: =SUMIF(A1:A10,"Apple",B1:B10) sums where column A contains "Apple". Wildcards: =SUMIF(A1:A10,"*apple*",B1:B10) matches any text with "apple". Case-insensitive by default.

Why is my SUMIF returning 0?

Common causes: 1) No values meet criteria 2) Text numbers (stored as text not numbers) 3) Extra spaces in data 4) Wrong range references 5) Criteria syntax error. Check data types and use TRIM to remove spaces.

How to use SUMIF with dates?

Use date criteria with operators: =SUMIF(A1:A10,">="&DATE(2025,1,1),B1:B10) sums where dates >= Jan 1, 2025. Or reference cell: =SUMIF(A1:A10,">="&D1,B1:B10) where D1 contains date. Always use & to concatenate operators with cell references.

SUMIF Function in Excel: Complete Tutorial with Examples (2025)

Q: Can SUMIF have multiple criteria?

No, SUMIF supports one criterion only. Use SUMIFS for multiple criteria: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). Example: =SUMIFS(C:C, A:A, "Apple", B:B, ">100") sums column C where A="Apple" AND B>100.

Updated: December 2025•10 min read

Quick Answer: SUMIF sums with one condition: =SUMIF(range, criteria, sum_range). Example: =SUMIF(A2:A10,">100",B2:B10) sums B values where A>100. Use criteria: numbers (>50), text ("Apple"), dates (>=DATE(2025,1,1)). For multiple criteria, use SUMIFS.

What is SUMIF?

SUMIF is an Excel function that adds numbers based on a condition. Unlike SUM which adds everything, SUMIF only sums values that meet your specific criteria.

Basic Example:

// Sum all sales over $100
=SUMIF(B2:B10, ">100")

// Sum sales for "Product A"
=SUMIF(A2:A10, "Product A", B2:B10)

// Sum sales from specific region
=SUMIF(C2:C10, "North", B2:B10)

Numbers

>100, <=50, =0

Text

"Apple", "*berry"

Dates

>=DATE(2025,1,1)

💡 When to Use SUMIF

• Sum sales for a specific product or region
• Calculate total expenses above a threshold
• Add values for a specific date range
• Total amounts for a particular category

SUMIF Syntax Explained

=SUMIF(range, criteria, [sum_range])

1. range (required)

The cells you want to check against your criteria.

A2:A100 ← Check these cells

2. criteria (required)

The condition that cells must meet.

">100" ← Greater than 100

"Apple" ← Exact text match

">="&D1 ← Greater than or equal to cell D1

3. sum_range (optional)

The actual cells to sum. If omitted, Excel sums the range.

B2:B100 ← Sum these cells

Common Criteria Operators

Comparison Operators:

> (greater than)
< (less than)
>= (greater than or equal)
<= (less than or equal)
= (equal to)
<> (not equal to)

Wildcard Characters:

* - any number of characters
? - single character
"*apple*" - contains "apple"
"A*" - starts with "A"

10+ SUMIF Examples

1. Sum Values Greater Than 100

=SUMIF(B2:B50, ">100")

Sums all values in B2:B50 that are greater than 100.

2. Sum Sales for Specific Product

=SUMIF(A2:A50, "Laptop", C2:C50)

Sums values in C2:C50 where A2:A50 contains "Laptop".

3. Sum with Cell Reference

=SUMIF(A2:A50, D2, B2:B50)

Sums B2:B50 where A2:A50 matches the value in cell D2.

4. Sum with Wildcard (Contains)

=SUMIF(A2:A50, "*apple*", B2:B50)

Sums B2:B50 where A2:A50 contains the text "apple".

5. Sum Not Equal To

=SUMIF(A2:A50, "<>Cancelled", B2:B50)

Sums B2:B50 where A2:A50 is not "Cancelled".

6. Sum with Date Criteria

=SUMIF(A2:A50, ">="&DATE(2025,1,1), B2:B50)

Sums B2:B50 where dates in A2:A50 are on or after Jan 1, 2025.

7. Sum Between Two Values

=SUMIF(B2:B50, ">=100") - SUMIF(B2:B50, ">200")

Sums values between 100 and 200 (100-200 inclusive).

8. Sum Cells That Are Not Blank

=SUMIF(A2:A50, "<>")

Sums all non-blank cells in A2:A50.

SUMIF vs SUMIFS

SUMIF (1 Criterion)

=SUMIF(A:A, "North", B:B)

SUMIFS (Multiple Criteria)

=SUMIFS(B:B, A:A, "North", C:C, ">100")

Use SUMIFS when you need AND logic with multiple conditions.

Frequently Asked Questions

Why is my SUMIF formula returning 0?

Common causes: 1) No values meet your criteria, 2) Numbers stored as text (add VALUE() or convert to numbers), 3) Extra spaces (use TRIM function), 4) Data type mismatch, 5) Incorrect range references. Check your criteria syntax and data types.

Can SUMIF work with multiple columns?

SUMIF checks one column but can sum from a different column. For multiple criteria across different columns, use SUMIFS instead. Example: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2).

How do I use SUMIF with OR logic?

SUMIF doesn't support OR natively. Use multiple SUMIF formulas: =SUMIF(A:A,"North",B:B)+SUMIF(A:A,"South",B:B). Or use SUMPRODUCT: =SUMPRODUCT((A:A="North")+(A:A="South"),B:B).

Can SUMIF criteria reference another cell?

Yes! Use & to concatenate: =SUMIF(A:A,">="&D1,B:B) where D1 contains your threshold. For text: =SUMIF(A:A,D1,B:B) directly references cell D1. Always use & for operators with cell references.

Does SUMIF work with entire columns?

Yes, SUMIF works with entire columns: =SUMIF(A:A,"Apple",B:B). This is useful for dynamic ranges. However, it may be slower with very large datasets. Use specific ranges (A2:A10000) for better performance.

How to use SUMIF with partial text match?

Use wildcards: * (any characters) or ? (single character). Examples: =SUMIF(A:A,"*apple*",B:B) contains "apple", =SUMIF(A:A,"A*",B:B) starts with "A", =SUMIF(A:A,"*berry",B:B) ends with "berry".

Generate SUMIF Formulas Automatically

Describe your summing needs in plain English and our AI creates the perfect SUMIF formula for you!

✓ No credit card required ✓ 5 free generations ✓ Perfect syntax every time

Related Formula Guides

Excel Formula Library

Browse 200+ Excel formulas by category

COUNTIF Function

Count cells that meet specific criteria

Coming Soon

IF Function Guide

Master conditional logic in Excel

Coming Soon