logic behind sumproduct

I typed all of this out already, then Excelforum decided it wanted to lock up and delete everything, to teach me the virtue of patience.


Anyways, here we go again.

It is an array formula. It gives you the Summed product of two arrays, so So sumproduct(a1:a3,b1:b3) would do this:
SUM(a1*b1,a2*b2,a3*b3)
Or,


In your example formula, it would do this

{c2=1, c3=1, c4=1,......} all the way to c22. Then result would be like the following,
{TRUE,TRUE,FALSE,TRUE,TRUE...}
Then you put a double negative in, --, which converts it to a 1 or a 0 depending on TRUE or FALSE
{1,1,0,1,1...}

Then the other part,
{e2>=d2, e3>=d3, e4>=d4...e22>=d22}
it will also return true's and falses, lets say
{1,0,1,0,1...}

Now, we multiple them, which is the PRODUCT part

{1*1,1*0,0*1,1*0,1*1...}
which is
{1,0,0,0,1...}

Then we SUM those 0's and 1's to get the end result, which in this case is
1+0+0+0+1 = 2

Hope this helps!~



Edit; technically those should be semicolons between the values and not commas, oh well.

Semicolon notes new row, comma is new column

so {0,1;2,3;4,5} would represent a table of

0	1
2	3
4	5

Originally Posted by ammartino44

Could someone explain the logic behind this formula? Pretty much how it works.
=SUMPRODUCT(--($C$2:$C$22=1),--($E$2:$E$22>=$D$2:$D$22))

The formula says: count the number of rows 2 through 22 where the value in column C equals 1 and the value in column E equals the value in column D.

The expression $C$2:$C$22=1 returns an array of TRUE or FALSE. So does the expression $E$2:$E$22>=$D$2:$D$22.

The double-negate (--) converts each to an array of 1 for TRUE and 0 for FALSE because otherwise, SUMPRODUCT would ignore the TRUE and FALSE logic values.

Any equivalent arithmetic would do the same; for example, ($C$2:$C$22=1)*1 and ($C$2:$C$22=1)+0. So alternatively, we could write
=SUMPRODUCT(($C$2:$C$22=1)*($E$2:$E$22>=$D$2:$D$22))

In either case, SUMPRODUCT multiplies the array values row-by-row, which results in an array of 1 where both conditions are TRUE and 0 where either or both conditions are FALSE.

The sum of the resulting array is effectively a count of when both conditions are TRUE.

@J.U.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.