Thanks:  0
Likes:  0

# Thread: need help counting w/multiple conditions

1. 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 ]

2. in K2,

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

this should get you started

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

4. 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

5. 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".

6. 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 ]

7. 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?

8. 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

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

10. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•