This post is not about basics in SUMIF function but about some hacks in that function. So, let's dive into the topic.,
The Arguments of SUMIF formula is ...
=SUMIF(range,criteria,[sum_range])
But, You have to read the Arguments as
=SUMIF(Criteria_range, criteria, sum_range)
Hack 1:
In Below picture, SUMIF function used to get the sum of sales for East Asia & Pacific in cell G3.
Region data fed in Criteria Range Argument as B3:B12,
Then for Criteria Argument, Single cell value fed from F3,
And for Sum range Argument, single cell value fed from C3.
Sales of East Asia & Pacific Region = 1,33,86,63,302.
Details:
In Excel, when we handle more than one range or Array in formulas, basic requirement is that all range or array has to be the same dimension. But here in this case, Criteria range is the range with 10 cells, but the sum range is the single cell which is C3. The hack here is that SUMIF function programmed to match the size based on Criteria range.
So here, Sum_Range argument in this function will pick C3 as start cell and for the last cell it will get the attribute from criteria range and will form the range as C3:C12.
Once the ranges are of same size, then function will pick East Asia & pacific from B3:B12 and Corresponding values from C3:C12 as shown in Above pic and will deliver the result after Summing.
Hack 2:
In Below picture, in cell G3 SUMIF function used to get the sum of sales for regions which is blank.
Region data fed in Criteria Range Argument as B3:B12,
Then for Criteria Argument, “=” is fed,
And for Sum range Argument, sales range fed from C3:C12.
Sales for the criteria Region is blank = 1,33,86,63,302.
Details:
Here, to find the sum of sales, where the region range is blank, you have to use = sign within double quotes in criteria arguments in SUMIF Function.
Hack 3:
In Below picture, in cell G3 SUMIF function used to get the sum of sales where the sales amount is greater than 350.
Sales data fed in Criteria Range Argument as $C$4:$C$11,
Then for Criteria Argument, G1 is fed,
When sum range and criteria range are same then we can ignore the sum range because,
SUMIF is programmed to get sum range from criteria range if sum range is omitted.
Commentaires