Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: need help counting w/multiple conditions

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    in K2,

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

    this should get you started
    :: Pharma Z - Family drugstore ::

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


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

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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