SUMPRODUCT() question

[John McGraw]

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!

 

[Dave Hawley]

I agree! It most certainly is

 

[Chris Davison]

*sigh*

yup, it's very probable I'll be using 250 criteria ummmmm vertically

ie if one of my criteria labels is "Project" there will be 200+ of them listed downwards

I had been using a VLOOKUP to check them, but it meant 25 minutes calculation time and helped swell the filesize to over 14megs...

Dave's DSUM and DCOUNT funtions look like making my tax checking life much easier

 

[Yogi Anand]

Hi John and Dave, ... :
I am going to pick up here where Dave suggested using the Database functions in Excel ... couple of things, we can use the DCOUNT function and we don't have to do use the sum function at all, and 2) we don't need two crireria, we can have the two conditions to be fulfilled in a single criterion. So, with

COL A = {01;06;07;01;08;03;05}
COL B = {01;11;12;01;01;01;03}

so if my data is in cells A18:B25, and my criterion is in cells E18:F20 (head1>5, or head2>10), then my formula to get the count to satisfy the criterion is:

=DCOUNT(A18:B25,1,E18:E20) ... resulting in 3
or
=DCOUNT(A18:B25,2,E18:E20) ... resulting in 3

Well, what do you think guys ... I didn't miss anything here did I? please do post your comments back!