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.
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
[Watch on YouTube] / [Subscribe to our YouTube Channel]Let’s look at how to freeze a row in Excel. It can be a real pain when you don’t have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns.
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data
Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another. For example, if I’m creating a personal cash flow worksheet and at the end of the month I
Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few.
Learn how to insert subtotal rows into sorted data without having to spend time doing it manually. Recently I ran a training session for an Accounts Manager and her staff. They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at every change