SUM and SUMIF function are very common and useful for the people who deal with Ms-Excel. Ms-Excel has a wide range of functions. Recently I learn about SUMIFS() function.
SUMIFS was introduced with MS-Excel 2007 and it works similar to SUM and SUMTIF i.e. it returns sum total of rows. The best thing about SUMIFS is it gives us functionality to apply multiple filtering criteria. It mainly has three parameters.
SUMIFS(sum_range, criteria_range1, criteria)
In the sum_range parameter we need to supply the cell range or column for which we want to return sum total.. If your entire data is in 5 rows and 5 columns (A to E column) and you want to return Sum of 5th column then you can pass E1:E5 as sum_range parameter.
In the criteria parameter we need to supply the condition or data that we are searching for. The best thing about criteria parameter is it accepts string and logical expression(>, <, =).
Let us understand it with a basic example. We have following data in the excel sheet.
Now if we have to find out sum total of Quantity Vendor Samsung supplies. We can use SUMIFS() function. We can pass the three parameters as discussed above.
=SUMIFS(E3:E18,$C$3:$C$18,"=Samsung")
This will return a sum total of 190 So Samsung is the vendor who is supplying 190 items in quantities.
Now, let us find out the sum total of quantity that Samsung supply which costs above $200
=SUMIFS(E3:E18,$C$3:$C$18,"=Samsung",D3:D18,">200")
This will return a sum total of 156. So Samsung is the vendor for 156 items which costs more than $200.
Now, let us find out sum total of products that Samsung supply which costs above $200 and item quantity is more than 80.
=SUMIFS(E3:E18,$C$3:$C$18,"=Samsung",D3:D18,">200",E3:E18,">80")
This will return a sum total of 90. So Samsung is the vendor who is supplying 90 items of for two products which costs more than $200 and has quantity more than 10.
So as you can see SUMIFS() function can accepts multiple criteria. It supports up to 27 range and criteria.
The important thing to consider about SUMIFS function is that the range should be same in all multiple criteria that we apply.
