If you’re comfortable using SUMIF Functionin Microsoft Excel You might find it interesting to know about Excel SUMIFS Function that is a way to summarize the values that satisfy two or more requirements.
Function of Excel SUMIFS
As you’re likely aware that the SUMIF Function of Excel is a tool to determine the sum, count or the total of items that meet certain criteria or requirements.
Similar to that to the SUMIFS Function of Excel can be used to calculate the Sum or the total of items that satisfy multiple specifications or requirements.
For example, if it is data on sales for different kinds of Gadgets that are sold at two stores The SUMIFS Function can be utilized to determine the number of iPads that are sold at the initial store.
In this case in the above scenario, the SUMIFS Function only applies in the event that Gadget type is iPad. Gadget model is the iPhone (First Criteria) and is available in the store called the First (Second Criteria).
1. The Syntax of Excel SUMIFS Function
The Syntax of the SUMIFS Function is described in the following.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)
Sum_range A range that contains Cells you want to add.
Criteria_range1 Where the element that meets the first Criteria is situated.
Criteria1 First condition, or criteria, SUMIFS will only be performed when the criteria is valid.
Criteria_range2 The range that contains the second item which meets the second Criteria can be found.
Criteria 2: Second condition of SUMIFS is performed when the criteria is real.
Like you could see above the Syntax of SUMIFS function lets the user to specify various criteria and locations in the range in which each of the criteria are placed.
2. How to Use the SUMIFS Function in Excel
Look over the table below that provides the sales figures of Microsoft Windows Computers, ChromeBooks and MacBooks that are sold in two different stores (Store#1 as well as Store#2).
The objective is to calculate the total quantity of MacBooks sold in Store#1 by using the SUMIFS Function within Excel.
1. Place the cursor in cell B14 then type =SUMIFS. Microsoft Excel will give you the Syntax to follow. ( =SUMIFS(sum_range, criteria_range1 1, criteria1, criteria_range2 the criteria2). ,…).
NOTE: Cell B14 is the place where you will print the results.
2. Going by the Syntax, select B1:B12 in the range of sums. These are where the items you wish to add are in (number of products sold).
3. Then, select A1:A12 as the criteria_range1 – this is the place where the first selection criteria (MacBook) is located.
4. Select the First Criteria by clicking on the cell A5 that contains the word “MacBook”..
5. Select Cells E1:E12 as criteria_range2 – – this is where the second requirement (Store#1) is located.
6. Now, select the second Criteria by clicking on the cell E3 with the words “Store#1”.
7. Finally, close the bracket, then press the Enter key.
The SUMIFS formula instantly displays that number of MacBooks sold by Store#1 within cell B14.