How to Use SUMIF in Microsoft Excel

Read Time:1 Minute, 46 Second

Adding numbers together in Microsoft Excel is a basic calculation that can use the SUM function. What if you want to add those values but only if they meet certain conditions? This is when the SUMIF function comes in.

With SUMIF, you can add the values in the cells you specify as long as they meet specific criteria. Maybe you want to find the total sales but only for certain products or the total revenue but only for particular locations.

If your Excel sheet is set up in a way that your calculation isn’t easily determined, the SUMIF function and its formula can help.

Use SUMIF For a Single Cell Range

The syntax for the function is SUMIF(cell_range, criteria, sum_range) where the first two arguments are required. Because sum_range is optional, you can add numbers in one range that correlate to criteria in another.

To get the basic feel of the function and its arguments, let’s start by using a single range of cells without the optional argument.ADVERTISEMENT

You could add values in a cell range only if they are greater than a certain amount. Enter the following formula, replacing the cell references and criteria with your own.

=SUMIF(C2:C7,">25000")

This formula adds the numbers in the cell range C2 through C7 only if they are greater than 25,000.

SUMIF using greater than for a single cell range

On the flip side, you can add numbers that are less than a certain amount using this formula:

=SUMIF(B2:B7,"<10000")

This adds the numbers in cells B2 through B7 only if they are less than 10,000.

SUMIF using less than for a single cell range

For one more example, you can add numbers that are the same amount with this formula:

=SUMIF(A2:A7,"5000")

This adds the numbers in cells A2 through A7 only if they are exactly 5,000.

SUMIF using equals for a single cell range

Use SUMIF With Number Criteria for Multiple Ranges

Now let’s put that conditional argument to work, sum_range. Here we’re calculating expenses and revenue. With SUMIF, we can calculate the revenue for locations whose expenses meet our criteria and vice versa.

Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Check out our other content

Leave a Reply

Recent Articles

More like this
Related

%d bloggers like this: