Are you sure that your SumProduct formula did not work?
A very small example and your exact formula would be useful.
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!
Are you sure that your SumProduct formula did not work?
A very small example and your exact formula would be useful.
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,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 ]
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.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
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.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.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
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!
Try:
=SUMPRODUCT((1*((A1:A7>5)+(B1:B7>10))))-SUMPRODUCT((A1:A7>5)*(B1:B7>10))
Aladin
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 ]
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 ]
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.
400... man, those are a lot of criteria !
Like this thread? Share it with others