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.
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).
|Range||This range holds the data you are wanting to evaluate against your criteria.|
|Criteria||The 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_range||The 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.
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.
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.
If you found this post helpful please ‘Like’ us!
If you enjoyed this you may also enjoy
- Hide errors in a worksheet
- How do I sum a range of cells that include #N/A or #DIV/0! errors?
- Finding and removing duplicate data in Excel
- 5 Reasons why your Excel filter may not be working