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 Patton]

Are you sure that your SumProduct formula did not work?

A very small example and your exact formula would be useful.

[John McGraw]

The top SUMPRODUCT() did exactly what I wanted. So, yes it works. But I'm trying to do the same thing, except with "OR" logic, instead of "AND". The multiplication of the 2 arrays produces a 1 when both values are true and a 0 otherwise. (So each row is counted when column A >5, _AND_ column B >10)

What I want, is the two arrays to be evaluated, but to produce a 1 when EITHER
condition is true, not just both.

So column A has 50 rows, column B has 50 rows. Each is full of numbers. I want to count each row that contains a value >5 in column A, _OR_ >10 in column B.

Like I said, I am looking to sumproduct, because this seems impossible with countif().
Maybe its impossible with sumproduct as well.
Thats my question.

I hope I have been clear. Sorry if I havent.

Also, I dont have a real idea for a formula on how to do this... The formula in my above post (the sumproduct with +), is the only thing I could think of trying. It obviously doesnt do the job.



[ This Message was edited by: John McGraw on 2002-03-18 23:32 ]

[Aladin Akyurek]

On 2002-03-18 23:23, John McGraw wrote:
The top SUMPRODUCT() did exactly what I wanted. So, yes it works. But I'm trying to do the same thing, except with "OR" logic, instead of "AND". The multiplication of the 2 arrays produces a 1 when both values are true and a 0 otherwise. (So each row is counted when column A >5, _AND_ column B >10)

What I want, is the two arrays to be evaluated, but to produce a 1 when EITHER
condition is true, not just both.

So column A has 50 rows, column B has 50 rows. Each is full of numbers. I want to count each row that contains a value >5 in column A, _OR_ >10 in column B.

Like I said, I am looking to sumproduct, because this seems impossible with countif().
Maybe its impossible with sumproduct as well.
Thats my question.

I hope I have been clear. Sorry if I havent.

Also, I dont have a real idea for a formula on how to do this... The formula in my above post (the sumproduct with +), is the only thing I could think of trying. It obviously doesnt do the job.

[ This Message was edited by: John McGraw on 2002-03-18 23:32 ]

Hi John,

The SUMPRODUCT version would be:

[1]

=SUMPRODUCT(1*((A1:A50>5)+(B1:B50>10)))

However,

[2]

=COUNTIF(A1:A50,">5")+COUNTIF(B1:B50,">10")

will compute the same result.

Aladin

[John McGraw]

On 2002-03-18 23:45, Aladin Akyurek wrote:
Hi John,

The SUMPRODUCT version would be:

[1]

=SUMPRODUCT(1*((A1:A50>5)+(B1:B50>10)))

However,

[2]

=COUNTIF(A1:A50,">5")+COUNTIF(B1:B50,">10")

will compute the same result.

Aladin

Thanks Aladin. But I dont think it works. I'm afraid that maybe I wasnt clear enough. I'm sorry for that.

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

I only put 0's before single digits so the values would line up and be easier to compare.

Now, if I count A>5 OR B>10; The answer would be 3. But:

=SUMPRODUCT(1*((A1:A7>5)+(B1:B7>10)))

would produce the answer of 5. It is counting rows with two values true twice, thats not OR logic. The countif() solution does the same thing.

I hope I make sense. If a1>5, and b1>10, I only want to count that row once, not twice. Traditional OR logic.

Thanks much for the help, it is appreciated greatly!

[Aladin Akyurek]

On 2002-03-19 00:18, John McGraw wrote:

Thanks Aladin. But I dont think it works. I'm afraid that maybe I wasnt clear enough. I'm sorry for that.

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

I only put 0's before single digits so the values would line up and be easier to compare.

Now, if I count A>5 OR B>10; The answer would be 3. But:

=SUMPRODUCT(1*((A1:A7>5)+(B1:B7>10)))

would produce the answer of 5. It is counting rows with two values true twice, thats not OR logic. The countif() solution does the same thing.

I hope I make sense. If a1>5, and b1>10, I only want to count that row once, not twice. Traditional OR logic.

Thanks much for the help, it is appreciated greatly!

Yep, you got me there.

Try:

=SUMPRODUCT((1*((A1:A7>5)+(B1:B7>10))))-SUMPRODUCT((A1:A7>5)*(B1:B7>10))

Aladin

[Dave Hawley]

Hi John

I haven't followed this thread but try this:

Let's say Column A has the Heading "Head1" and B the heading "Head2"

Copy these headings to cells to D1:E1
then in cell D2 put: >5 and cell E2 put: <10

Again copyn the 2 headings, this time to cells F1:G1
then in cell F2 put: >5 and cell G2 put: >10


Now in any cell put:
=SUM(DCOUNT(A1:B8,"Head1",D1:E2),DCOUNT(A1:B8,"Head1",F1:G2))

The database Functions offer a lot of flexibility and can be seen here:
http://www.ozgrid.com/download/default.htm
Download: DFunctionsWithValidation.zip

With a bit of imagination you can make are very user friendly spreadheet that will extract your data from a data table that needs to meet up to 256 Criteria.



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-19 02:48 ]

[Chris Davison]

Dave,

is that any 256 criteria or 256 rows of criteria in the criteria range ?

I assume that if we had, say, 400, we could just split it into 2 seperate D functions ?

*please* say yes !

Chris


edit.... oh, 256 criteria columns in the criteria range, you mean ?

[ This Message was edited by: Chris Davison on 2002-03-19 05:35 ]

[Dave Hawley]

Hi Chris

Yes I mean 256 columns of table headings and 256 criteria under each. But as you say you could use 2 database functions.

[Juan Pablo González]

400... man, those are a lot of criteria !