Sometimes you want to get the sum of items in a list based upon certain criteria. For example in a column of sales, you might want the sum of sales for a particular product. To achieve this use SUMIFS. Note: there is also a function called SUMIF, this will also work, but SUMIFS does the same thing and you can use it in more situations. The Parameters for SUMIFS are:
The list of values you want to add.
The critera (this is the value you want to search for. only cells with this value will be added)
The list of values that contain the criteria you are searching for
In the above we want to add only the sales for Watley products.
Click in the cell where you want the total (B13 in the example)
Type =sumifs
Press Enter for the result.
Drag and highlight the column with the values to be added (C2 to C10 in the example)
Type , (comma)
Drag and highlight the values to search for the criteria (A2 to A10 in the example)
Type , (comma)
Click the cell with the criteria (A13 in the example)
Press Enter for the result.
Your formula should look like this:
The real power of the SUMIFS function is in handling multiple criteria. You simply add extra parameters to the formula. Here's an example where we need to get the sum of sales for the product Watleys and the item Watches:
The steps for doing this are:
Click in the cell where you want the total (C13 in the example)
Type =sumifs
Press Enter for the result.
Drag and highlight the values which are to be added (C2 to C10 in the example)
Type , (comma)
Drag and highlight the values you want to match to the first criteria (A2 to A10 in the example)
Type , (comma)
Click the cell with the first criteria (A13 in the example)
Type , (comma)
Drag and highlight the column the values you want to match to the second criteria (B2 to B10 in the example)
Type , (comma)
Click the cell with the second criteria (B13 in the example)
Press Enter for the result.
AVERAGEIFS
AVERAGEIFS will give you the average of a list of values based upon one or more criteria.
The Steps are pretty much the same as SUMIFS. In the following example we want the average sales for Aeon Tables:
Click in the cell where the you want the Average (C8 in the example)
Type =averageifs
Press Enter for the result.
Drag and highlight the values to be averaged (C2 to C5 in the example)
Type , (comma)
Drag and highlight the values you want to match to the first criteria (A2 to A5 in the example)
Type , (comma)
Click the cell with first criteria (A8 in the example)
If you want the average for only one criteria (Aeon), you would press Enter for the result, otherwise go to the next step.
Type , (comma)
Drag and highlight the values you want to match to the second criteria (B2 to B5 in the example)
Type , (comma)
Click the cell with second criteria (B8 in the example)
Press Enter for the result.
COUNTIFS
Finally there's the function COUNTIFS, COUNTIFS will count how many items are in a list depending on the criteria given. For example, I want to know how many Armstrong products were sold:
Click in the cell where the you want the count (B13 in the example)
Type =countifs
Press Enter for the result.
Drag and highlight the values you want to match to the criteria (A2 to A10 in the example)
Type , (comma)
Click the cell with criteria to search for (A13 in the example)
Press Enter for the result.
Like SUMIFS, and AVERAGEIFS, COUNTIFS can handle multiple criteria. Suppose you want to count how many Armstrong chairs were sold:
Click in the cell where the you want the qty (C13 in the example)
Type =countifs
Press Enter for the result.
Drag and highlight the values you want to match to the first criteria (A2 to A10 in the example)
Type , (comma)
Click the cell with first criteria (A13 in the example)
Drag and highlight the values you want to match to the second criteria (B2 to B10 in the example)
Type , (comma)
Click the cell with second criteria (B13 in the example)
