SUMPRODUCT() question

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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Are you sure that your SumProduct formula did not work?

A very small example and your exact formula would be useful.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
On 2002-03-19 00:18, John McGraw wrote:
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!

Yep, you got me there.

Try:

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

Aladin
 
Upvote 0
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
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-19 02:48
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top