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

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

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 :/

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

Bit long yes, but it works! Thank you Weazel!