need help counting w/multiple conditions

Kevuas

New Member
Joined
Apr 19, 2002
Messages
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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

:)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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