How to use Sumifs function in Excel
Hello and Welcome to Klick2learn…
In this post, we are about to explain SUMIFS function in MS Excel….
So what is SUMIFS Function???
The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria.
For example, we can use SUMIFS function to sum the transaction amount for a (1) particular product and a (2) particular transaction.
Great… So how can we apply SUMIFS function???
Lets have a look on the syntax of SUMIFS function:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Lets understand each attribute of this syntax:
- sum_range := The range of cells which we want to sum.
- criteria_range1 := That range in which we have our first criteria (i.e. range of product column)
- criteria1 := The criteria that defines which cells in Criteria_range1 will be added (i.e. “Laptop” if we want to add transaction amount for Laptops only)
- criteria_range2 := That range in which we have our second criteria (i.e. range of Transaction column)
- criteria2 := The criteria that defines which cells in Criteria_range2 will be added (i.e. “Purchase” if we want to add only Purchase transactions)
Similarly we can give multiple criteria based on our requirement.
Lets apply it in Excel….
Suppose we have data like this:
Now to find the sum of amount for Purchase Transaction of Laptop, apply formula as follows:
Lets understand each attribute of this formula:
- $C$2:$C$19 is sum_range of amount (this is the range for which we want to sum the amount)
- $B$2:$B$19 is Criteria_range1 (Range in which we have first criteria of Purchase)
- “Purchase” is Criteria1
- $A$2:$A$19 is Criteria_range2 (Range in which we have second criteria of Laptop)
- “Laptop” is Criteria1
For more clarity watch the video below on the same.