Hi,
I have used SUMPRODUCT() and SUMIFS() functions to return the sum based on multiple conditions and both are working similar. Please help me to understand the differences between these two functions.
Thx,
Mukesh
Hi,
I have used SUMPRODUCT() and SUMIFS() functions to return the sum based on multiple conditions and both are working similar. Please help me to understand the differences between these two functions.
Thx,
Mukesh
Yes, both functions can be used to return sum against multiple conditions. SUMIFS() was introduced in Excel 2007. Prior to this, SUMPRODUCT() was the function to return sum against multiple conditions.
SUMPRODUCT() is an array function and it performs various tasks other than returning sum against multiple conditions like returning count,rank and returning the sum of products. Let’s look at some examples:
a)Return the sum of sales for Male employee and also count the number of male employees.
We can achieve both tasks by using SUMIF/ SUMIFS and COUNTIF/ COUNTIFS and array formula also. Let’s do it with SUMPRODUCT.
The sum of SALES: “=SUMPRODUCT (($C$2: $C$7=“Male”) *1, ($E$2: $E$7))” and the result is 179.
How it works: -
Here, we have two arrays, but first array has conditional output of TRUE or FALSE because we are camparing each value with “Male” and second array has corresponding sales values. To check it, press F9 after selection of formula component ($C$2: $C$7=“Male”) then it will look like
=SUMPRODUCT (({TRUE; FALSE; FALSE; TRUE; TRUE; TRUE}) *1, ($E$2: $E$7)), here TRUE for Male and FALSE for Female.
We have multiplied it by 1 to convert TRUE to 1 and FALSE to 0 as we know that we can’t do mathematical calculation if both the argument is not numeric. We can convert TRUE/ FALSE to numeric in multiple ways like:
Adding dual negative sign before the array (First negative sign convert it to number and second one make it positive)
=SUMPRODUCT (–($C$2: $C$7=“Male”), ($E$2: $E$7))
Using function N () to convert it to a number
=SUMPRODUCT (N (($C$2:$C$7=“Male”)), ($E$2:$E$7))
Multiplying with second / numeric arrays
=SUMPRODUCT (($C$2: $C$7=“Male”) * ($E$2: $E$7))
Here we have two arrays, one with 1 or 0 and another one of sales values: -
=SUMPRODUCT ({1;0;0;1;1;1}, ({49;38;31;46;49;35}))
It works like “149+038+031+146+149+135” and result is 179.
Similarly for Count of Male Employee, need to remove the sum range.
=SUMPRODUCT (($C$2: $C$7="Male") *1)
=SUMPRODUCT (({TRUE; FALSE; FALSE; TRUE; TRUE; TRUE}) *1)
=SUMPRODUCT ({1;0;0;1;1;1})
=4
b)Return the sum of sales for Male employee those belongs to department admin and region North.
It is almost similar to the above example only difference is the addition of arrays for other criterion and it can also be achieved by function SUMIFS (Introduced in excel 2007).
=SUMPRODUCT (--($C$2:$C$7="Male"),--($D$2:$D$7="Admin"),--($B$2:$B$7="North"), ($E$2:$E$7))
OR
=SUMPRODUCT (($C$2:$C$7="Male")*1, ($D$2:$D$7="Admin")*1, ($B$2:$B$7="North")*1, ($E$2:$E$7))
OR
=SUMPRODUCT (($C$2: $C$7="Male") * ($D$2: $D$7="Admin") * ($B$2: $B$7="North") * ($E$2: $E$7))
Let’s look at the formula evaluation: -
=SUMPRODUCT ({1;0;0;1;1;1}, {1;0;0;0;1;0}, {1;0;0;1;0;0}, {49;38;31;46;49;35})
= (11149+00038+00031+10146+11049+10035)=49
c)Return the sum of sales for Male employee those belongs to department Admin OR Sales.
Like an array function, we can also use ‘+’ sign as OR operator and ‘*’ sign as AND.
=SUMPRODUCT (($C$2: $C$7="Male") * (($D$2: $D$7="Admin") + ($D$2: $D$7="Sales")) * ($E$2: $E$7))
Here we have used ‘+’ sign between ($D$2: $D$7=“Admin”) and ($D$2: $D$7=“Sales”) to work as either one of the statement is true.
Let’s look at the formula evaluation: -
=SUMPRODUCT ({1;0;0;1;1;1} * ({1;0;0;0;1;0} + {0;1;0;0;0;1}) * ({49;38;31;46;49;35}))
Evaluated ‘+’ sign
=SUMPRODUCT ({1;0;0;1;1;1} * ({1;1;0;0;1;1}) * ({49;38;31;46;49;35}))
= (1149+0138+0031+1046+1149+1135) =133
d)Let’s return the rank of each employee based on sales.
It can be done with the help of RANK function now let’s look at how SUMPRODUCT() can calculate rank.
Put this formula in cell against an employee (Row number 2): -
=SUMPRODUCT (($E$2: $E$7>E2) *1) +1
Let’s look at the formula evaluation: -
Here we know that, why we have multiplied by 1, now look at the one level down at the formula:
=SUMPRODUCT (({49; 38; 31; 46; 49; 35} >E2) *1) +1
For each employee, it has above syntax E2 will change to E3, E4… E7. Now evaluates it for first row and it will look like:
=SUMPRODUCT (({49; 38; 31; 46; 49; 35} >49) *1) +1
Now compare each value of the array with 49 and then it will appear as….
=SUMPRODUCT (({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}) *1) + 1
After Multiplying by 1, =SUMPRODUCT ({0; 0; 0; 0; 0; 0}) +1, Here 1 is added because if no value is greater than current value then the output of SUMPRODUCT would be zero and addition of 1 make it rank 1.
What it has checked? How many values are greater than the current value in the given array if assumes three values are greater then rank would be 3+1=4.
Now copy the formula to rest of the records, it will provide similar results as the RANK function provides.
Hope this helps!
Regards,
Sunil