# logic behind sumproduct

1. ## logic behind sumproduct

Could someone explain the logic behind this formula? Pretty much how it works. Thanks

=SUMPRODUCT(--(\$C\$2:\$C\$22=1),--(\$E\$2:\$E\$22>=\$D\$2:\$D\$22))

2. ## Re: 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

3. ## Re: logic behind sumproduct

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.

4. ## Re: logic behind sumproduct

Hi,

Maybe you will be able to help me. I have small issue, the same one as I found here: http://www.excelforum.com/excel-prog...osed-file.html

I used formula SUMIFS and it works perfectly but I used two separate files so when I close one I have error.

Solution was to change formula for SUMPRODUCT but unfortunately I don't know how.

=SUMIFS('[File Name.xlsx]Sheet Name'!H:H;'[File Name.xlsx]Sheet Name'!\$A:\$A;\$A4;'[File Name.xlsx]Sheet Name'!\$B:\$B;\$B4;'[File Name.xlsx]Sheet Name'!\$D:\$D;\$D4)

I will be very Grateful for you answer!
Thanks.

5. ## Re: logic behind sumproduct

@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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1