How to Use SUMIF in Excel

SUMIF is one of the most useful Excel functions when you want to add numbers that meet one condition. Microsoft says you use SUMIF to sum the values in a range that match criteria you specify, such as summing only numbers larger than 5 or summing sales for a certain person.

This makes SUMIF especially useful for budgets, sales reports, score sheets, inventory tables, and any spreadsheet where you need totals based on one rule. If you need more than one condition, Microsoft says that is when SUMIFS should be used instead.

The Short Answer

The basic SUMIF formula in Excel is:

=SUMIF(range, criteria, [sum_range])

Microsoft says:

range is the set of cells Excel checks against your condition
criteria is the rule Excel uses
sum_range is the range Excel adds if the condition is met

How SUMIF Works

Microsoft says SUMIF evaluates a range using your condition, then adds the matching values. It also notes that the sum_range is optional, which means Excel can sometimes sum the same range it is testing.

That means SUMIF has three main parts:

range = the cells Excel tests
criteria = the condition
sum_range = the cells Excel adds when the condition matches

Simple SUMIF Example

A very common example is summing numbers above a certain value.

Microsoft gives this example:

=SUMIF(B2:B25,">5")

Microsoft says this adds only the values in B2:B25 that are larger than 5.

This is useful when you want to total only values above a threshold.

SUMIF with Text Criteria

SUMIF is also very useful for matching text.

Microsoft gives this example:

=SUMIF(B2:B5,"John",C2:C5)

Microsoft says this sums the values in C2:C5 where the corresponding cells in B2:B5 equal John.

This is useful for:

sales by employee
totals by category
department-based totals
project-based totals

How to Use SUMIF with Numbers

You can use number conditions like:

greater than
less than
equal to

Example:

=SUMIF(A2:A20,">100",B2:B20)

This tells Excel:

check A2:A20
find rows where the value is greater than 100
add the matching values from B2:B20

Microsoft’s SUMIF syntax supports numeric criteria written in quotation marks like ">5".

How to Use SUMIF with Text

Text-based SUMIF formulas are often used for categories or names.

Example:

=SUMIF(A2:A20,"Apples",B2:B20)

This adds the values in B2:B20 only where A2:A20 equals Apples.

Microsoft’s examples show the same pattern with text criteria such as a person’s name.

How to Use SUMIF with Dates

Microsoft notes that the selected range may contain dates in standard Excel format. That means SUMIF can also be used with date-based conditions when the cells are stored as real dates.

Example:

=SUMIF(A2:A20,">=01/01/2026",B2:B20)

This can be useful for:

totals after a certain date
monthly tracking
deadline-based reports

Because date formatting can vary by system, it is important that the cells are true Excel dates, not text entries. That is an inference based on Microsoft’s note that dates should be in standard Excel format.

SUMIF vs SUMIFS

This is one of the most important distinctions.

Microsoft says:

use SUMIF when you want to sum values with one condition
use SUMIFS when you want to sum values with multiple conditions

So:

use SUMIF for something like total sales of Apples
use SUMIFS for something like total sales of Apples in the North region

FAQ

What is SUMIF in Excel?

Microsoft says SUMIF is used to sum the values in a range that meet criteria you specify.

What is the syntax of SUMIF?

Microsoft says the syntax is:

=SUMIF(range, criteria, [sum_range])

What is the difference between SUMIF and SUMIFS?

Microsoft says SUMIF is for one condition, while SUMIFS is for multiple conditions.

Why is my SUMIF formula not working correctly?

Microsoft says common issues include trying to match strings longer than 255 characters and certain error-value cases. It also notes that incorrect or misaligned ranges can lead to wrong results.

Make spreadsheet work easier with genuine Office keys, instant delivery, and quick activation that gets Excel ready when you need it most.