List of Unique Numbers Based on Multiple Criteria

[TonyB1965]

I need to look at data and return a list of unique numbers from column C where Column A, B match certain criteria. And a different set of returns where A,B, and D match certain criteria.

I have data that basically looks like this:

	A	B	C	D
1	Criteria1	Criteria 2	Criteria 3	Criteria 4
2	h	r	2
3	h	p	22	c
4	h	r	2
5	h	p	2
6	h	r	8
7	h	p	25	c
8	h	r	8
9	h	p	22
10	h	r	2
11	h	p	26
12	h	r	7
13	h	p	0
14	v	r	12
15	v	p	88	c
16	v	r	13
17	v	p	88	c
18	v	r	12
19	v	p	88
20	v	r	13
21	v	p	15
22	v	r	18
23	v	p	3	c
24	v	r	20
25	v	p	4	c
26	v	r	26

<tbody>
</tbody>

And I need results like this:

	F
1	Result 1 (Cirteria 1=h and Criteria 2=r)
2	2
3	8
4	7

<tbody>
</tbody>

F 22 Result 5 (Cirteria 1=h and Criteria 2=p and Criteria 4 =c) 23 22 24 25 <tbody> </tbody>

<tbody>
</tbody>

Would greatly prefer to use formulas and not VBA.

Thanks in advance if you can help.

 

[Eric W]

See if this works for you:

Book1
	A	B	C	D	E	F	G	H	I
1	Criteria1	Criteria 2	Criteria 3	Criteria 4		Criteria 1	Results	Criteria 1	Results
2	h	r	2			h	2	h	22
3	h	p	22	c		Criteria 2	8	Criteria 2	25
4	h	r	2			r	7	p
5	h	p	2					Criteria 4
6	h	r	8					c
7	h	p	25	c
8	h	r	8
9	h	p	22
10	h	r	2
11	h	p	26
12	h	r	7
13	h	p	0
14	v	r	12
15	v	p	88	c
16	v	r	13
17	v	p	88	c
18	v	r	12
19	v	p	88
20	v	r	13
21	v	p	15
22	v	r	18
23	v	p	3	c
24	v	r	20
25	v	p	4	c
26	v	r	26
Sheet3

Cell Formulas
Range	Formula
I2	=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$26)/(($A$2:$A$26=H$2)*($B$2:$B$26=H$4)*($D$2:$D$26=H$6)*(COUNTIF(I$1:I1,$C$2:$C$26)=0)),1)),"")
G2	=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$26)/(($A$2:$A$26=F$2)*($B$2:$B$26=F$4)*(COUNTIF(G$1:G1,$C$2:$C$26)=0)),1)),"")

 

[TonyB1965]

Works perfectly. Thank you very much.

 

[Eric W]

You're welcome.