Enter the data into cells D1 to F11 of an Excel worksheet, as seen in the image above. The SUMIFS function and the search criteria (less than 275 orders and sales agents from the East sales region) goes in row 12 below the data. The syntax for the SUMIFS function is: The data that we enter into the blank lines in the dialog box will form the arguments of the SUMIFS function. These arguments tell the function what conditions we are testing for and what range of data to sum when it meets those conditions. In this tutorial, the data for the Sum_range argument goes in the Total Sales column.
Tutorial Steps
The Criteria_range1 argument indicates the range of cells the SUMIFS is to search when trying to match the first criteria: the East sales region.
Tutorial Steps
Although actual data, such as the word East, can be entered into the dialog box for this argument it is usually best to add the data to a cell in the worksheet and then input that cell reference into the dialog box.
Tutorial Steps
How Cell References Increase SUMIFS Versatility
If a cell reference, such as D12, is entered as the Criteria Argument, the SUMIFS function will look for matches to whatever data is in that cell in the worksheet. So after finding the sales amount for the East region, it will be easy to locate the same data for another sales region simply by changing East to North or West in cell D12. The function will automatically update and display the new result. As with the Criteria1 argument, we will enter the cell reference to Criteria2’s location into the dialog box rather than the data itself. An answer of zero (0) will appear in cell F12 (the cell where we entered the function) because we have not yet added the data to the Criteria1 and Criteria2 fields (C12 and D12). Until we do, there is nothing for the function to add up, and so the total stays at zero. For help with this example see the image above. The answer $119,719.00 should appear in cell F12. Only two records, those in rows 3 and 4 match both criteria and, therefore, only the sales totals for those two records are summed by the function. The sum of $49,017 and $70,702 is $119,719. When you click on cell F12, the complete function =SUMIFS(F3:F9,D3:D9,D12,E3:E9,E12) appears in the formula bar above the worksheet. The SUMIFS argument looks for specific criteria in two or more fields in the record and only if it finds a match for each field specified is the data for that record summed up. In the SUMIF step by step tutorial, we matched the single criterion of sales agents who had sold more than 250 orders in a year. In this tutorial, we will set two conditions using SUMIFS: that of sales agents in the East sales region who had fewer than 275 sales in the past year. Setting more than two conditions can be done by specifying additional Criteria_range and Criteria arguments for SUMIFS. All arguments in this function are required. Sum_range — the data in this range of cells is summed when a match is found between all specified Criteria and their corresponding Criteria_range arguments. Criteria_range — the group of cells the function is to search for a match to the corresponding Criteria argument. Criteria — this value is compared with the data in the corresponding. Criteria_range — actual data or the cell reference to the data for the argument.
