Sum If vs SumProduct

[johnanish]

I have this spreadsheet where we calculate times developers spend in a fiscal week.

One of my friends came up with this formula which I am unable to understand. Instead of using sumif, he used a sumproduct with a --.

A breakdown of this would be really appreciated.

 

[Hui]

Have a read of:
http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
and especially:
http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

 

[Hui]

In your example at I3:
=SUMPRODUCT(--($A$3:$A$21=G3),$B$3:$B$21)

Simply says add up all of Column B when Column A = G3 = "Development"

If you edit the formula
=SUMPRODUCT(--($A$3:$A$21=G3),$B$3:$B$21)
select the Red section and press F9
Excel will show it as
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

This is showing that status of each comparison
eg: A3=G3 then A4=G3 then A5=G3 up to A21=G3
You can see that the first 5 values are True and the rest are False

Believe it or Not Excel doesn't understand how to do maths on a Boolean (True/False)
so by adding the -- Excel converts the True/False to 1/0's

Now select
=SUMPRODUCT(--($A$3:$A$21=G3),$B$3:$B$21) and press F9

Excel will display
{1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Note that the Trues have been converted to 1 and Falses to 0
Multiplying the True by - - is the same as saying True * -1 * -1 which is 1
Multiplying the False by - - is the same as saying True * -1 * -1 which is 0

You could have also used 1*(
Which I think is much easier to read

If you select =SUMPRODUCT(--($A$3:$A$21=G3),$B$3:$B$21)
Excel will display
{0.685636574074074;0.0210416666666667;0.249583333333333;0.402604166666667;2.95105746527778;0.147951388888889;0.0355092592592593;0;0.0022337962962963;0.166666666666667;0;0.120393518518519;0.341909722222222;0.208333333333333;2.0259837962963;0.868009259259259;0;0.135694444444444;0}

Which is the array of values in Column B

Sumproduct then does a Sumproduct on the two arrays
Adding up the Products of the Two arrays.

It could have been simplified as: =SUMPRODUCT(($A$3:$A$21=G3)*$B$3:$B$21)

I will also direct you to:

http://chandoo.org/wp/2011/12/21/formula-forensics-no-007/
&
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
Which has a good description of How Sumproduct works