Use the SUMIF function to total only the cells that match your requirement

Posted June 20, 2018

 

Use the SUMIF function to total only the cells that match your requirement.

For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a lot of time.

Prior to learning this function most people filter their data based on their requirement, e.g. the team member’s name, and then copy and paste the info into another worksheet. Once in the new worksheet they then use the SUM function to create the total.

Even though this works there are several problems with using this method.

One is, it takes a long time. Another is that the data is being removed from the original source. What if the data in the original table is updated? You will need to go through the entire process again to update your totals.

Yikes!

Learn the SUMIF function and you can avoid double-handling.

Using the SUMIF function

The SUMIF function totals only the cells within specified range that meet given criteria. The function is written =SUMIF(range,criteria,sum_range).

Formatting ToolDescription
RangeThis range holds the data you are wanting to evaluate against your criteria.
CriteriaThe criteria in the form of a cell reference, number, e.g. 32, expression, e.g. “>32” or “>”&M2, or text, e.g. “Total”. With text wildcards can be used, e.g. “Total*” would find all cells where the word “total” is at the beginning of a string of text, e.g. Total March, Total April, etc.
Sum_rangeThe range of cells you want to total if their corresponding cells in Range match the criteria.  If Sum_range is omitted, all cells in Range are totaled.

 

To create a SUMIF

1. First select the cell that will hold the result.

2. Insert the SUMIF function. In the Range box enter the range you want to evaluate. In the Criteria box set the criteria you are searching for. (Optional) In the Sum_Range box enter the range you want to total based on the set criteria and then click OK.

 

In the Example 1 below, SUMIF has been used to only add sales that are greater than $1000 to the total. When the ‘sum_range’ is omitted the cells in the ‘range’ part of the function are totaled.

BTW, it’s a good idea to use the Insert Function dialog box to insert SUMIF functions until you become familiar with how Excel requires the arguments to be inserted. You will see that the criteria in the formula below has quotation marks placed around it, “>1000”.

This is a requirement for the function to work properly. When you use the Insert Function dialog box to insert the function Excel pops the quotation marks in for you.

Sumif function in Excel

Example 1

In Example 2 below, the range L5:L24 contains the names of the salesperson responsible for each of the sales and the range K5:K24 contains value of each sale.

Using the SUMIF function you can quickly find the total sales for an individual sales person.

The function in cell L2 looks down the range L5:L24. When Excel finds a match for the content in cell L1, i.e. Barry’s name, it jumps across to the sum_range in column K and adds the value of Barry’s sale to the total. Therefore, using the SUMIF function the total value of all of Barry’s sales is found.

Sumif formula in Excel

Example 2

 

Using the SUMIFS function

Use SUMIFS function to total a range based on multiple criteria. In the example below only ‘Fresh Fruit’ sales made by ‘Barry’ would be included in the total.

Just be aware that SUMIFS asks for the Sum_Range first and then you must enter the criteria ranges and criteria. If you need to enter more than 2 criteria just use vertical the scroll bar to scroll down and you will see more criteria boxes.

Excel Sumif formula

Was this blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>