Excel — Learn how to use the SUMIFS function.
If you typically use SUMIF to return a total for one item in a list — take you Excelling to a new level by switching to SUMIFS!
SUMIF — To sum cells based on a single criterion.
SUMIFS — To sum cells based on a single criterion or multiple criteria — up to 127.
Best practice and to future proof your Excel skills just start using SUMIFS.
You can easily learn new function by using Excel’s built-in formula assistance wizard.
1. Click in a cell.
2. Click on the insert function icon on the top bar (blue arrow below)
3. Type in “SUMIFS” and click on GO.
4. Highlight SUMIF in the list and click OK.
5. This launches the Function Arguments window. This helps you craft the correct formula with hints for each part of the SUMIFS function to sum a range filtered on one or more criteria:
6. Let’s refer to this sample spreadsheet to illustrate how SUMIFS works. Let’s find out how many units of cameras were sold in USA.
7. Sum-range — The number range we want to add up.
a. We want to know the number of Camera Units Sold in the USA.
b. Sum_range would be Units Sold or column D (D2:D200).
8. Criteria_range1 — This is the list that contains our criteria to filter results from. First we want to find all the Cameras.
Criteria_range1 we would select Products or column B (B2:B200)
Criteria1 — we would enter “Camera” or a Cell Reference containing Camera
9. Criteria_range2 — This is the next list that contains our criteria to filter results from. Next we want to find units sold in USA.
Criteria_range2 we would select Country or column A (A2:A200)
Criteria2 — We would enter “USA” or a Cell Reference containing USA
Using the Wizard will help as you are learning this formula — you will be alerted to errors, get hints, and see the result as you add each criterion.
You can also write the formula without using the Function Arguments Wizard. Begin by typing your formula “=SUMIFS(” and Excel will give you guidance.
- Begin formula = SUMIFS (
2. Add Sum_range for Units sold (D2:D200,
3. Add first Criteria_range1 (Products) and Criteria1 (Camera)
4. Add second Criteria_range2 (Country) and Criteria2 (USA)
Give this a try next time you need total for only a select portion of your data. This is just the beginning of what you can do with SUMIFS.
