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

Thread: Unique values with multiple conditions

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello everybody ,

    I find myself confronted to a wierd problem ;

    I wish to count in a list all the unique values that meet four different
    criterias .
    So far I managed to workout the folowing formula :
    {Sum(if($C$53:C$4983>=$F$4)*($C$53:$C$4983<=$J$4)*($I$53:$I$4983=$D$4)*($B$53:$B$4983=E5);1/countif(F53:F4983;F53:F4983))}
    but it doesn't work

    Any ideas

    thanks

    david

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about doing countif in 4 different cells then adding them up?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Consider the following; array enter it on a test range or edit for your information.

    =SUM(IF((C4:C7>=C1)*(C4:C7<=C2)*(I4:I7=D1)*(B4:B7=B1),1,0))

    This will count how many meet the 4 criteria.
    Extend to determine how many are unique.

    [ This Message was edited by: Dave Patton on 2002-04-12 07:22 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    2 ideas

    1. Determine by row if the data meets the criteria

    =IF((C4>=$C$1)*(C4<=$C$2)*(I4=$D$1)*(B4=$B$1)=1,C4,"")

    This puts the amount in say Column G

    Then array enter the following to determine how many are unique

    =SUM(IF(LEN(G4:G7),1/COUNTIF(G4:G7,G4:G7)))


    2. use an array formula (I used named ranges but that is not essential)

    =SUM((MATCH(rC_,rC_,0)=(ROW(rC_)-MIN(ROW(rC_))+1))*(rB_=B1)*(rI_=D1))

    Revise the references as necessary.

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
  •