need help counting w/multiple conditions

Kevuas · Apr 28th, 2002, 04:53 PM

Help!

I am trying to count all the occurrances of a multiple condition worksheet, with the data arranged as:

A1 : W or L
A2 : X%

B1 : W or L
B2 : X%

... up to 100 or some rows.

I want to count all the occurrances of a "W" with a certain percentage, say ">50%". I want all "L"s to be ignored.

The problem is that the % is entered as a calculation from the formula below:

A2 = IF(ISNUMBER(X1),X1/some total number,""),

so that it returns an empty cell instead of "0%" when there is no number in X1.

I can't figure out how to do a bi-conditional comparison to count all occurrances of a "W" and ">50%" because of the formula. I tried the suggestions below but have not been successful yet.

[ This Message was edited by: Kevuas on 2002-04-28 17:04 ]

Chris Davison · Apr 28th, 2002, 05:01 PM

in K2,

=SUMPRODUCT((A1:I1="W")*(A2:I2>50%))

this should get you started

Kevuas · Apr 28th, 2002, 05:04 PM

[ This Message was edited by: Kevuas on 2002-04-28 17:04 ]

Chris Davison · Apr 28th, 2002, 05:09 PM

no worries !

your data can't extend very far (ie max 255 columns) so this formula is okay...

If you like what it does, I'd recommend you do a search on this site for "SUMPRODUCT", particularly Aladin's work

and also Database Functions if you're going to need the same sorts of enquiries on larger databases with lots of rows of info

Kevuas · Apr 28th, 2002, 05:22 PM

Ok, I might have made a mistake explaining this (sorry I'm new). What I want to count is the times when BOTH conditions are met. In other words, I want a bi-conditional statement, where A1 HAS to be a "W" and A2 HAS to be over 50%. It seems as if the previous formula only counts "W' and over 50% regardless of position.

Another note: the cells might or might not contain a %, but they will always have a "W' or a "L".

Chris Davison · Apr 28th, 2002, 05:27 PM

are you sure you typed it correctly ?

it will only count when BOTH conditions are met.....a "W" and >50%

Are you percentages actually numbers ? Test by =ISNUMBER(A2) if it returns FALSE you have a problem and willneed to convert those textual percentages to numeric values

try this :

=SUMPRODUCT((A1:I1="W")*((A2:I2)*1>50%))

[ This Message was edited by: Chris Davison on 2002-04-28 16:32 ]

Kevuas · Apr 28th, 2002, 05:35 PM

Ok, that is the problem. I have a conditional statement to calculate the %. Because I didn't want cells to show up as "0%", (I only want cells to calculate a percent when there is actually a percent to be calculated), I use the statement :

A2 = IF(ISNUMBER(X1),X1/another number,"")

So that it returns a blank cell when there is no number. It only does the calculation when there is a number in that cell. This explains why I have been having trouble working with it. Any way you might suggest to handle this?

Chris Davison · Apr 28th, 2002, 05:38 PM

Try my above edit....

(gotta run off to bed now... 1am over here)

Someone else will be able to help if the above doesn't work, I'm sure of it

Kevuas · Apr 28th, 2002, 05:48 PM

Hmmm.. getting a #VALUE error for the revised formula.

Aladin Akyurek · Apr 28th, 2002, 09:22 PM

Quote:

On 2002-04-28 16:48, Kevuas wrote:

Hmmm.. getting a #VALUE error for the revised formula.

Try:

=SUMPRODUCT((A1:L1="W")*(ISNUMBER(A2:L2))*(A2:L2>50%))

Formula-returned blanks are bound to give problems. Better avoid using such formulas when the returned results are subject to further computations.

Aladin

Apr 28th, 2002, 05:01 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	in K2, =SUMPRODUCT((A1:I1="W")(A2:I2>50%)) this should get you started __________________ :: Pharma Z - Family drugstore ::*

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 28th, 2002, 04:53 PM	#1
Kevuas New Member Join Date: Apr 2002 Location: UCONN Posts: 8	Help! I am trying to count all the occurrances of a multiple condition worksheet, with the data arranged as: A1 : W or L A2 : X% B1 : W or L B2 : X% ... up to 100 or some rows. I want to count all the occurrances of a "W" with a certain percentage, say ">50%". I want all "L"s to be ignored. The problem is that the % is entered as a calculation from the formula below: A2 = IF(ISNUMBER(X1),X1/some total number,""), so that it returns an empty cell instead of "0%" when there is no number in X1. I can't figure out how to do a bi-conditional comparison to count all occurrances of a "W" and ">50%" because of the formula. I tried the suggestions below but have not been successful yet. [ This Message was edited by: Kevuas on 2002-04-28 17:04 ]

Apr 28th, 2002, 05:04 PM	#3
Kevuas New Member Join Date: Apr 2002 Location: UCONN Posts: 8	[ This Message was edited by: Kevuas on 2002-04-28 17:04 ]

Apr 28th, 2002, 05:09 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	no worries ! your data can't extend very far (ie max 255 columns) so this formula is okay... If you like what it does, I'd recommend you do a search on this site for "SUMPRODUCT", particularly Aladin's work and also Database Functions if you're going to need the same sorts of enquiries on larger databases with lots of rows of info

Apr 28th, 2002, 05:22 PM	#5
Kevuas New Member Join Date: Apr 2002 Location: UCONN Posts: 8	Ok, I might have made a mistake explaining this (sorry I'm new). What I want to count is the times when BOTH conditions are met. In other words, I want a bi-conditional statement, where A1 HAS to be a "W" and A2 HAS to be over 50%. It seems as if the previous formula only counts "W' and over 50% regardless of position. Another note: the cells might or might not contain a %, but they will always have a "W' or a "L".

Apr 28th, 2002, 05:27 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	are you sure you typed it correctly ? it will only count when BOTH conditions are met.....a "W" and >50% Are you percentages actually numbers ? Test by =ISNUMBER(A2) if it returns FALSE you have a problem and willneed to convert those textual percentages to numeric values try this : =SUMPRODUCT((A1:I1="W")((A2:I2)1>50%)) [ This Message was edited by: Chris Davison on 2002-04-28 16:32 ]

Apr 28th, 2002, 05:35 PM	#7
Kevuas New Member Join Date: Apr 2002 Location: UCONN Posts: 8	Ok, that is the problem. I have a conditional statement to calculate the %. Because I didn't want cells to show up as "0%", (I only want cells to calculate a percent when there is actually a percent to be calculated), I use the statement : A2 = IF(ISNUMBER(X1),X1/another number,"") So that it returns a blank cell when there is no number. It only does the calculation when there is a number in that cell. This explains why I have been having trouble working with it. Any way you might suggest to handle this?

Apr 28th, 2002, 05:38 PM	#8
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Try my above edit.... (gotta run off to bed now... 1am over here) Someone else will be able to help if the above doesn't work, I'm sure of it

Apr 28th, 2002, 05:48 PM	#9
Kevuas New Member Join Date: Apr 2002 Location: UCONN Posts: 8	Hmmm.. getting a #VALUE error for the revised formula.