John McGraw
Board Regular
- Joined
- Feb 25, 2002
- Messages
- 76
A while back, on this board Aladin kindly demonstrated for me how to use sumproduct as a conditional count better and more powerful than countif(). I think I am getting the hang of how it works, but need some further help. I do the following to count where two conditions in two seperate columns are true:
=SUMPRODUCT((A1:A50>5)*(B1:B50>10))
I think what happens is that each array is evaluated, then the two are multiplied. So 1*1 (for true values) would = 1. And 0*0 or 0*1 would = 0. Then they are all added together, to produce a count where ">5" AND ">10" are true. Am I right so far on the mechanisms of how this works?
I am also curious on how I could perform an "or" count in the same fashion. I thought about using:
=SUMPRODUCT((A1:A50>5)+(B1:B50>10))
But the problem is that the 1+1's = 2. Thus giving an incorrect final count.
I tried using countif() and OR(), but I couldnt get it to work. So I am looking to sumproduct() for a solution. Am I looking in the right place?
Thanks for any help!
=SUMPRODUCT((A1:A50>5)*(B1:B50>10))
I think what happens is that each array is evaluated, then the two are multiplied. So 1*1 (for true values) would = 1. And 0*0 or 0*1 would = 0. Then they are all added together, to produce a count where ">5" AND ">10" are true. Am I right so far on the mechanisms of how this works?
I am also curious on how I could perform an "or" count in the same fashion. I thought about using:
=SUMPRODUCT((A1:A50>5)+(B1:B50>10))
But the problem is that the 1+1's = 2. Thus giving an incorrect final count.
I tried using countif() and OR(), but I couldnt get it to work. So I am looking to sumproduct() for a solution. Am I looking in the right place?
Thanks for any help!