Results 1 to 5 of 5

Thread: Issue with multiple criteria for a specific range consisting of 3 columns, Excel 2007

  1. #1

    Issue with multiple criteria for a specific range consisting of 3 columns, Excel 2007



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there.

    I have a problem where I'm trying to count the number of Names(cells) that have a P1 or P2 value of <-10 or >10, and are of either type 1 or 0. Finally, a Name should not be counted twice. I've tried to use the countifs formula, but it returned 0. I got around that with a sum formula, but not without still getting Names(cells) counted several times. Anyone know how to get around this issue without using VBA? Help will be much appreciated.
    A B C D
    1 Name Type P1 P2
    2 a 1 -21 66
    3 b 1 -13 34
    4 c 1 -19 101
    5 d 0 -4 12
    6 e 1 -8 31
    7 f 1 -13 66
    8 g 1 -7 28

  2. #2
    Acolyte Weazel's Avatar
    Join Date
    Jul 2014
    Location
    Florida
    Posts
    26
    Articles
    0
    Excel Version
    2016
    based on your data and the assumption that either P1 is <-10 or P2 is >10 this formula would return 7. My question would be do you mean -10 or +10 in either column? so could you have values in P1 >10 and values in P2 <-10 ? If thats the case this formula probably wouldn't work.

    =SUM(IF(FREQUENCY(IF((C2:C8<-10)+(D28>10),MATCH(A2:A8,A2:A8,0)),ROW(A2:A8)-ROW(A2)+1),1)) control shift enter

  3. #3
    It is formulated a bit bad I'm sorry, also since all of the names in the example meet at least one of the criterias. I'll try and be a bit more specific.

    P1 criterias: <-10, >10
    P2 criterias: <-10, >10
    Results i need to find via formulas: 7 (the number of names that meet at least one of the criterias), 1 (the number of names that meet at least one of the criterias and are type 0), 6(the number of names that meet at least one of the criterias and are type 1)

    What I tried myself was using the following formula: =SUM(COUNTIFS(C28,{"<-10",">10"})) this yields 11, which of course is because of the names that meet the criteria in both P1 and P2. So one way could be to figure out how to subtract the names that meet criterias both in P1 and P2 (4), and then somehow match the type (0/1) afterwards? hmm :/

  4. #4
    Acolyte Weazel's Avatar
    Join Date
    Jul 2014
    Location
    Florida
    Posts
    26
    Articles
    0
    Excel Version
    2016
    I'm thinking theres probably a better way but....

    =SUM(IF(FREQUENCY(IF((C2:C8<-10)+(C2:C8>10)+(D2: D8<-10)+(D2: D8>10),MATCH(A2:A8,A2:A8,0)),ROW(D2: D8)-ROW(D2)+1),1)) control shift enter

    seems to work

    I added some spaces in the ranges to take care of the smiley's so you will probably need to fix them

  5. #5
    Bit long yes, but it works! Thank you Weazel!

Posting Permissions

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