SUMIF Function in Excel: Ultimate Guide

The SUMIF function in Excel is one of the most useful functions for calculating totals based on a specific condition. It allows you to add numbers that meet a particular criterion without manually filtering your data or calculating each value separately.

For example, you can use SUMIF to calculate:

• Total sales for a specific product
• Revenue generated by a salesperson
• Expenses for a department
• Sales above a specific amount
• Transactions before or after a certain date
• Values associated with text that partially matches a keyword

In this SUMIF Function in Excel: Ultimate Guide, you’ll learn the syntax, arguments, criteria, comparison operators, dates, wildcards, practical examples, common mistakes, and the difference between SUMIF and related Excel functions.

---

What Is the SUMIF Function in Excel?

The SUMIF function adds cells that meet a single specified condition.

For example, imagine you have the following sales data:

Product	Sales
Laptop	1,200
Monitor	500
Laptop	1,500
Keyboard	200
Monitor	700

To calculate the total sales for Laptop, use:

=SUMIF(A2:A6,"Laptop",B2:B6)

The formula checks the cells in A2:A6 for the word Laptop and adds the corresponding values from B2:B6.

The result is:

2,700

Because:

1,200 + 1,500 = 2,700

---

SUMIF Syntax in Excel

The syntax of the SUMIF function is:

=SUMIF(range, criteria, [sum_range])

The function has three arguments:

Argument	Required	Description
range	Yes	The range of cells that Excel evaluates against the criterion
criteria	Yes	The condition that determines which cells should be included
[sum_range]	No	The range containing the numbers to be added

Example

=SUMIF(A2:A10,"East",B2:B10)

This formula means:

Check A2:A10 for “East” and add the corresponding values from B2:B10.

The sum_range argument is optional. If it is omitted, Excel sums the cells in range itself.

For example:

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

This adds the values in B2:B10 that are greater than 1,000.

---

How Does SUMIF Work?

The SUMIF function essentially performs three steps:

1. Checks the criteria range
2. Determines which cells meet the condition
3. Adds the corresponding values from the sum range

For example:

=SUMIF(A2:A7,"Laptop",B2:B7)

Excel examines each cell in A2:A7.

If the cell contains Laptop, Excel adds the corresponding value from column B.

This makes SUMIF particularly useful when working with large datasets.

---

SUMIF Function Examples and Criteria

The following examples demonstrate the most important ways to use SUMIF in Excel.

1. SUMIF With Text Criteria

One of the most common uses of SUMIF is adding values based on text.

Suppose you have:

Department	Expenses
Marketing	1,200
IT	2,500
Marketing	1,800
HR	900
IT	1,500

To calculate the total expenses for Marketing:

=SUMIF(A2:A6,"Marketing",B2:B6)

Result

3,000

Excel finds every occurrence of Marketing in column A and adds the corresponding values from column B.

When to use this technique

This is useful for calculating:

• Sales by product
• Revenue by salesperson
• Expenses by department
• Orders by customer
• Revenue by region
• Costs by project

Text criteria should normally be enclosed in quotation marks:

"Marketing"

2. SUMIF Using a Cell Reference

Instead of typing the criterion directly into the formula, you can store it in a worksheet cell.

For example, suppose D2 contains:

Marketing

You can use:

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

Excel uses the value in D2 as the criterion.

If you change D2 from Marketing to IT, the formula automatically calculates the total for IT.

Why use a cell reference?

This approach is particularly useful for:

• Interactive reports
• Excel dashboards
• Management summaries
• Drop-down lists
• Dynamic calculations

For example, you could create a drop-down list in D2 containing:

Marketing
IT
HR
Finance
Sales

Then use:

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

The result changes automatically when the selected department changes.

3. SUMIF With Numbers

SUMIF can evaluate numerical values as well as text.

Suppose column B contains sales amounts:

Sales
500
1,500
800
2,200
1,200

To calculate the total of values greater than 1,000:

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

Result

4,900

The formula adds:

1,500 + 2,200 + 1,200

When the criteria range and sum range are the same, you can omit the third argument.

4. SUMIF With Greater Than

Use the > operator when you want to sum values greater than a specified number.

For example:

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

This adds all values greater than 1,000.

The condition is:

>1000

Example

If the values are:

Amount
400
750
1,100
1,500
2,000

The formula:

=SUMIF(A2:A6,">1000")

returns:

4,600

5. SUMIF With Less Than

Use the < operator to sum values below a specific number.

For example:

=SUMIF(B2:B100,"<500")

This adds all values less than 500.

If the range contains:

200
400
600
800
1,000

the formula returns:

600

because only 200 and 400 meet the condition.

6. SUMIF With Greater Than or Equal To

Use >= when the condition should include the specified value.

For example:

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

This adds all values that are:

• Greater than 1,000
• Equal to 1,000

For example, if the data contains:

500
1,000
1,500
2,000

the result is:

4,500

because 1,000, 1,500, and 2,000 satisfy the condition.

7. SUMIF With Less Than or Equal To

Use <= when you want to include values equal to the specified limit.

For example:

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

This adds values less than or equal to 1,000.

For example:

500
1,000
1,500
2,000

returns:

1,500

because 500 + 1,000 = 1,500.

8. SUMIF With Not Equal To

The <> operator means not equal to.

For example:

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

This adds all sales except sales associated with Laptop.

Suppose your data is:

Product	Sales
Laptop	1,000
Monitor	500
Keyboard	300
Laptop	1,500

The formula excludes the Laptop rows and returns:

800

because:

500 + 300 = 800

9. SUMIF With Equal To

You can explicitly use the = operator when you want to match a specific value.

For example:

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

This is equivalent to:

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

For text criteria, Excel generally allows you to omit the = sign.

Example

=SUMIF(A2:A10,"=Marketing",B2:B10)

calculates the total for Marketing.

10. SUMIF With Dates

SUMIF can be used to calculate totals based on dates.

Suppose you have:

Date	Sales
01/01/2026	500
05/01/2026	750
10/01/2026	900
15/01/2026	1,200
20/01/2026	800

To calculate sales after January 10, 2026:

=SUMIF(A2:A6,">"&DATE(2026,1,10),B2:B6)

The DATE function creates the date, while & combines the comparison operator with the date.

The condition becomes conceptually:

>10/01/2026

Excel then adds the corresponding sales values.

11. SUMIF With a Date in Another Cell

Instead of putting the date directly into the formula, you can store it in a worksheet cell.

Suppose D2 contains:

10/01/2026

Use:

=SUMIF(A2:A100,">"&D2,B2:B100)

This calculates the total sales after the date in D2.

If you change D2, the calculation updates automatically.

This approach is especially useful for interactive reports where users can select different dates.

12. SUMIF Between Two Dates

Calculating totals between two dates is a common Excel requirement.

For example, you might want to calculate sales between January 1 and January 31.

Because this requires two conditions, SUMIFS is generally more appropriate than SUMIF.

For example:

=SUMIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<="&DATE(2026,1,31))

This formula applies two conditions:

Date >= January 1, 2026
Date <= January 31, 2026

Why use SUMIFS?

SUMIF is designed around a single criterion, while SUMIFS can evaluate multiple criteria simultaneously.

Therefore:

One condition → SUMIF

Two or more conditions → SUMIFS

13. SUMIF With Wildcards

SUMIF supports wildcard characters, making it possible to perform flexible text matching.

The two main wildcard characters are:

Wildcard	Meaning
*	Any number of characters
?	Exactly one character

Wildcards are especially useful when you don’t know the complete text or when several values share a common pattern.

14. Using the Asterisk (*) Wildcard

The * wildcard represents any number of characters.

Suppose your product list contains:

Product	Sales
Laptop	1,000
Laptop Pro	1,500
Laptop Air	1,200
Desktop	900

To sum all products beginning with Laptop, use:

=SUMIF(A2:A5,"Laptop*",B2:B5)

This matches:

• Laptop
• Laptop Pro
• Laptop Air

It does not match Desktop.

The * tells Excel that any characters can appear after Laptop.

15. Using the Question Mark (?) Wildcard

The ? wildcard represents exactly one character.

For example:

=SUMIF(A2:A100,"A??",B2:B100)

This matches three-character values beginning with A.

For example:

ABC
A12
Axy

could match the pattern.

However:

ABCD

would not match because it contains four characters.

The question mark is useful when you know the general structure of a value but one or more characters may vary.

16. SUMIF With a Partial Text Match

You can combine wildcards with text to find a keyword anywhere inside a cell.

For example:

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

This can match values such as:

• Laptop
• Laptop Pro
• Business Laptop
• Gaming Laptop
• Professional Laptop

The asterisk before and after Laptop tells Excel that any characters can appear before or after the keyword.

Difference Between Laptop* and *Laptop*

"Laptop*"

means:

The cell starts with Laptop.

While:

"*Laptop*"

means:

The cell contains Laptop anywhere.

This distinction is important when working with product names, customer descriptions, categories, or imported text data.

---

SUMIF With Blank Cells

SUMIF can also be used to sum values associated with blank cells.

For example:

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

This adds values from B2:B100 where the corresponding cells in A2:A100 are blank.

To sum values where the criteria cells are not blank:

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

This can be useful when analyzing incomplete datasets.

---

SUMIF With Cell References and Comparison Operators

You can combine a cell reference with a comparison operator.

Suppose D2 contains:

1000

You might want to calculate the total of values greater than the number in D2.

Use:

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

The & operator joins the comparison operator > with the value in D2.

Conceptually, Excel evaluates the criterion as:

>1000

This technique allows you to create dynamic formulas without changing the formula itself.

---

SUMIF Across Different Worksheets

SUMIF can reference data stored on another worksheet.

Suppose the data is stored on a worksheet named Sales.

You can use:

=SUMIF(Sales!A2:A100,"Laptop",Sales!B2:B100)

This checks column A on the Sales worksheet and adds the corresponding values from column B.

If the worksheet name contains spaces, enclose the sheet name in single quotation marks:

=SUMIF('Sales Data'!A2:A100,"Laptop",'Sales Data'!B2:B100)

---

SUMIF With Excel Tables

Excel Tables make SUMIF formulas easier to read and maintain.

Suppose your table is named SalesTable and contains columns named:

• Product
• Sales

You can write:

=SUMIF(SalesTable[Product],"Laptop",SalesTable[Sales])

This is easier to understand than a formula containing large cell ranges.

Why use Excel Tables?

Tables automatically expand when new records are added, making them particularly useful for growing datasets.

---

SUMIF With Named Ranges

You can also use named ranges with SUMIF.

Suppose:

Products

refers to:

A2:A100

and:

Sales

refers to:

B2:B100

You can use:

=SUMIF(Products,"Laptop",Sales)

Named ranges can make formulas more readable, particularly in large workbooks.

---

SUMIF Is Not Case-Sensitive

SUMIF is not case-sensitive.

For example, the following values are generally treated as equivalent when matching text:

Laptop
laptop
LAPTOP

Therefore:

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

can match text with different capitalization.

If you specifically need case-sensitive criteria, SUMIF is not sufficient by itself. An alternative approach using functions such as SUMPRODUCT and EXACT may be required.

---

Common SUMIF Mistakes and How to Fix Them

1. Mismatched Ranges

A common mistake is using criteria and sum ranges that do not correspond properly.

Preferred:

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

Here, both ranges cover the same rows.

Keeping corresponding ranges aligned makes your formulas easier to understand and reduces unexpected results.

2. Forgetting Quotation Marks

Text criteria normally need quotation marks.

Incorrect:

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

Correct:

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

Unless Laptop is intentionally a cell reference or named range.

3. Incorrect Dynamic Criteria

Suppose D2 contains 1000.

This formula is incorrect:

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

Use:

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

The & combines the operator and cell value to create the required criterion.

4. Numbers Stored as Text

Sometimes imported data contains numbers stored as text.

For example:

"1000"

may look like a number but actually be text.

This can cause unexpected results in calculations.

If your SUMIF formula isn’t returning the expected result, check whether the numerical data is actually stored as numbers.

5. Extra Spaces in Text

Imported data may contain hidden leading or trailing spaces.

For example:

Laptop
Laptop

may appear identical but are not necessarily the same text value.

Cleaning your data with functions such as TRIM can help eliminate unnecessary spaces.

---

SUMIF vs SUM

The SUM function adds values without applying a condition.

For example:

=SUM(B2:B100)

adds every numerical value in the range.

SUMIF adds values only when a condition is satisfied:

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

Use SUM when:

You need the overall total.

Use SUMIF when:

You need a conditional total.

---

SUMIF vs SUMIFS

The main difference between SUMIF and SUMIFS is the number of conditions they can handle.

SUMIF

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

Uses one condition.

SUMIFS

=SUMIFS(C2:C100,A2:A100,"Laptop",B2:B100,"East")

Uses multiple conditions.

A simple rule to remember is:

SUMIF = one condition
SUMIFS = multiple conditions

---

SUMIF vs SUMPRODUCT

SUMPRODUCT can perform more advanced conditional calculations.

For straightforward conditional totals, SUMIF is usually easier to understand:

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

For more complex array-based logic, SUMPRODUCT may be more appropriate.

Therefore, choose the simplest function that correctly solves the problem.

---

Real-World SUMIF Examples

Example 1: Total Sales by Product

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

Calculates total sales for Laptop.

Example 2: Total Revenue by Salesperson

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

Calculates total revenue generated by Ahmed.

Example 3: Total Expenses by Department

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

Calculates total Marketing expenses.

Example 4: Orders Above $500

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

Calculates the total value of orders above $500.

Example 5: Products Starting With “Pro”

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

Calculates the total for products whose names begin with Pro.

Example 6: Dynamic Product Selection

If D2 contains a product name:

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

The formula calculates the total for whichever product is entered or selected in D2.

---

SUMIF for Excel Dashboards

SUMIF is particularly useful when creating Excel dashboards.

For example, you could create a summary table:

Product	Total Sales
Laptop	12,500
Monitor	8,700
Keyboard	4,200
Mouse	2,900

The Total Sales column can be calculated with SUMIF formulas.

For example:

=SUMIF(SalesTable[Product],A2,SalesTable[Sales])

You can then use these results to create:

• Column charts
• Bar charts
• KPI cards
• Management reports
• Sales dashboards
• Financial summaries

---

SUMIF Best Practices

Following a few best practices can make your SUMIF formulas more reliable and easier to maintain.

Use structured data

Keep each type of information in its own column.

For example:

Date	Product	Region	Sales
01/01/2026	Laptop	East	1,200
02/01/2026	Monitor	West	700

Use Excel Tables

Tables make formulas dynamic and easier to read.

Use cell references for dynamic criteria

Instead of:

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

consider:

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

when the criterion needs to change.

Use SUMIFS for multiple conditions

Don’t use complicated workarounds when SUMIFS is designed for the task.

Keep your source data clean

Check for:

• Extra spaces
• Inconsistent spelling
• Numbers stored as text
• Incorrect dates
• Duplicate categories

---

SUMIF Formula Cheat Sheet

Requirement	Formula
Sum based on text	=SUMIF(A2:A100,"Laptop",B2:B100)
Use a cell reference	=SUMIF(A2:A100,D2,B2:B100)
Greater than	=SUMIF(B2:B100,">1000")
Less than	=SUMIF(B2:B100,"<1000")
Greater than or equal to	=SUMIF(B2:B100,">=1000")
Less than or equal to	=SUMIF(B2:B100,"<=1000")
Not equal to	=SUMIF(A2:A100,"<>Laptop",B2:B100)
Equal to	=SUMIF(A2:A100,"=Laptop",B2:B100)
Begins with text	=SUMIF(A2:A100,"Laptop*",B2:B100)
Contains text	=SUMIF(A2:A100,"*Laptop*",B2:B100)
Blank criteria cells	=SUMIF(A2:A100,"",B2:B100)
Non-blank criteria cells	=SUMIF(A2:A100,"<>",B2:B100)
Another worksheet	=SUMIF(Sales!A2:A100,"Laptop",Sales!B2:B100)

---

Frequently Asked Questions About SUMIF

What is the SUMIF function in Excel?

SUMIF is an Excel function that adds values when they meet a specified condition.

What is the syntax of SUMIF?

The syntax is:

=SUMIF(range, criteria, [sum_range])

What is the difference between SUM and SUMIF?

SUM adds values without conditions, while SUMIF adds values that meet a specific criterion.

What is the difference between SUMIF and SUMIFS?

SUMIF is designed for a single criterion, while SUMIFS supports multiple criteria.

Can SUMIF work with text?

Yes. SUMIF can evaluate text criteria such as:

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

Can SUMIF work with dates?

Yes. SUMIF can evaluate dates and date-based conditions. For calculations involving multiple date conditions, SUMIFS is often more appropriate.

Can SUMIF use wildcards?

Yes. SUMIF supports the * and ? wildcard characters.

Is SUMIF case-sensitive?

No. SUMIF is not case-sensitive when evaluating text criteria.

Can SUMIF work across worksheets?

Yes. You can reference ranges located on another worksheet.

Can SUMIF use a cell reference as the criterion?

Yes. For example:

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

uses the value in D2 as the criterion.

---

Conclusion

The SUMIF function in Excel is an essential function for conditional data analysis. It allows you to quickly calculate totals based on text, numbers, dates, comparison operators, and wildcard patterns.

The basic syntax is:

=SUMIF(range, criteria, [sum_range])

The most important concept to remember is that SUMIF is designed primarily for one condition. When your calculation requires multiple conditions, SUMIFS is usually the better choice.

Once you understand text criteria, cell references, comparison operators, dates, wildcards, Excel Tables, and dynamic criteria, you can use SUMIF to build more efficient spreadsheets, reports, financial analyses, and Excel dashboards.