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

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

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