Today we are going to discuss about various methods in Excel to do sum with one criteria.
SUMIF and SIMIFS will fit best for our requirement, but we are also going to try with some other options.
Here, we have region wise sales data in the range B2:C19. Drop down provided in E4 cell to select regions. We are going to get sales amount for selected region in different methods.
Lot to learn, let's dive in.
Method 1:
Formula in F4 cell is,
=SUMIF($B$3:$B$19,$E4,$C$3:$C$19)
Arguments of SUMIF function are,
=SUMIF(range,criteria,[sum_range])
Read first argument as Criteria Range,
So here, the criteria range is B3:B19,
The criteria is E4 cell,
The sum range is C3:C19.
I have made a helper column 'Check' to explain how this function works,
As you can see in the range A3:A19, each cell in the criteria range B3:B19 is checked with our criteria in E4 cell. Apply filters in 'Check' column and only select TRUE. Now sum the values in C Column, that's how we got 737.234 in F4 cell. This is just the simulation of how SUMIF function works.
Method 2:
Formula in F4 cell is,
=SUMIFS($C$3:$C$19,$B$3:$B$19,$E4)
Arguments of SUMIFS are,
=SUMIFS(sum_range,criteria_range,criteria,...)
This is same as SUMIF function, but this function has the ability to handle multiple criteria.
So, it will do the sum with one criteria with ease.
Method 3:
Formula in F4 cell is,
=SUM(($B$3:$B$19=$E4)*$C$3:$C$19)
If you are using Excel 2016 or earlier version, press Ctrl + Shift + Enter. Then excel will house above formula with curly braces as shown below.
{=SUM(($B$3:$B$19=$E4)*$C$3:$C$19)}
Here, you can see that we are doing the same comparison as we did in method 1 with helper column. This comparison is the heart of doing this summing operation with different functions.
Logical comparison delivers TRUE's and FALSE as output. Then we multiply that with sum range. This mathematical operation will convert TRUE into 1 and FALSE into 0. One multiplied by corresponding sales amount will return the sales amount. Zero multiplied by corresponding sales amount will return zero. So, sales amount other than 'Central' will be excluded and the sales amount only belongs to 'Central' gets added through sum function.
Method 4:
Formula in F4 cell is,
=SUM(ISNUMBER(MATCH($B$3:$B$19,$E4,0))*$C$3:$C$19)
Try using CSE ( Ctrl + Shift + Enter ) if it throws error.
Again, we try to cook the same dish with difference ingredient.
Here, we use MATCH and ISNUMBER construction to get the Boolean values as output and we multiply this with sales amount column, then we do sum operation in resulting array.
Method 5:
Formula in F4 cell is,
=SUM(COUNTIF($E4,$B$3:$B$19)*$C$3:$C$19)
Try using CSE ( Ctrl + Shift + Enter ) if it throws error.
Here we get help from COUNTIF function to form the 1's and 0's construction and after that we do multiplication and sum operations.
Method 6:
Instead of SUM function in all the constructions we discussed here, we can use SUMPRODUCT function, which will allow you to enter without Ctrl + Shift + Enter keystroke.
Will share you the formula's here.
=SUMPRODUCT(($B$3:$B$19=$E4)*$C$3:$C$19)
=SUMPRODUCT(ISNUMBER(MATCH($B$3:$B$19,$E4,0))*$C$3:$C$19)
=SUMPRODUCT(COUNTIF($E4,$B$3:$B$19)*$C$3:$C$19)
Method 7:
This is Dynamic array for you,
The formula in F4 cell is,
=SUMPRODUCT(FILTER($C$3:$C$19,($B$3:$B$19=$E4)))
The core is same, here I filter the sales column only for the matching records in region column, then doing sum.
We can use any one of the above, based on the requirement.
Comments