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))
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))
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
Last edited by Speshul; 09-16-2014 at 02:39 PM.
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.
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.
Last edited by joeu2004; 09-16-2014 at 02:55 PM.
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.
Can you please help to change my formula from SUMIFS to SUMPRODUCT:
=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.
@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.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks