A formula that meets all conditions

[mully_10]

I'm trying to create a formula that meets all the conditions. Below is table that I manually enter data into.

[/URL][/IMG]

I'm trying to create a formula for each cell in the below table that will meet these conditions:

Looks in the PAD column for either 34H or 81G etc. Then is it "Remaining/Drilled" and in the "Target Zone" and either North or South for "Direction".

[/URL][/IMG]

Thanks

 

[Scott T]

COUNTIFS should work.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	PAD	WELL	rd	targ	dir
2	34H	1	d	Middle	s
3	34H	2	d	Lower	n
4	34H	3	r	Upper	n
5	34H	4	r	Middle	n
6	34H	5	d	Upper	n
7	81G	1	d	Upper	n
8	81G	2	d	Lower	n
9	81G	3	d	Middle	n
10	81G	4	d	Middle	n
11	81G	5	d	Middle	n
12	81G	6	r	Middle	s
13	81G	7	r	Upper	s
14
15
16		drilled upper		Drilled middle		Drilled lower		Remaining Upper		Remaining Middle		Remaining lower
17		north	south	north	south	north	south	north	south	north	south	north	south
18	34h	1	0	0	1	1	0	1	0	1	0	0	0
19	81g	1	0	3	0	1	0	0	1	0	1	0	0

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet35​

Worksheet Formulas Cell Formula B18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(B$17,1),$D$2:$D$13,RIGHT(B$16,LEN(B$16)-SEARCH(" ",B$16)),$C$2:$C$13,LEFT(B$16,1)) C18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(C$17,1),$D$2:$D$13,RIGHT(B$16,LEN(B$16)-SEARCH(" ",B$16)),$C$2:$C$13,LEFT(B$16,1)) D18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(D$17,1),$D$2:$D$13,RIGHT(D$16,LEN(D$16)-SEARCH(" ",D$16)),$C$2:$C$13,LEFT(D$16,1)) E18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(E$17,1),$D$2:$D$13,RIGHT(D$16,LEN(D$16)-SEARCH(" ",D$16)),$C$2:$C$13,LEFT(D$16,1)) F18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(F$17,1),$D$2:$D$13,RIGHT(F$16,LEN(F$16)-SEARCH(" ",F$16)),$C$2:$C$13,LEFT(F$16,1)) G18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(G$17,1),$D$2:$D$13,RIGHT(F$16,LEN(F$16)-SEARCH(" ",F$16)),$C$2:$C$13,LEFT(F$16,1)) H18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(H$17,1),$D$2:$D$13,RIGHT(H$16,LEN(H$16)-SEARCH(" ",H$16)),$C$2:$C$13,LEFT(H$16,1)) I18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(I$17,1),$D$2:$D$13,RIGHT(H$16,LEN(H$16)-SEARCH(" ",H$16)),$C$2:$C$13,LEFT(H$16,1)) J18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(J$17,1),$D$2:$D$13,RIGHT(J$16,LEN(J$16)-SEARCH(" ",J$16)),$C$2:$C$13,LEFT(J$16,1)) K18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(K$17,1),$D$2:$D$13,RIGHT(J$16,LEN(J$16)-SEARCH(" ",J$16)),$C$2:$C$13,LEFT(J$16,1)) L18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(L$17,1),$D$2:$D$13,RIGHT(L$16,LEN(L$16)-SEARCH(" ",L$16)),$C$2:$C$13,LEFT(L$16,1)) M18 =COUNTIFS($A$2:$A$13,$A18,$E$2:$E$13,LEFT(M$17,1),$D$2:$D$13,RIGHT(L$16,LEN(L$16)-SEARCH(" ",L$16)),$C$2:$C$13,LEFT(L$16,1)) B19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(B$17,1),$D$2:$D$13,RIGHT(B$16,LEN(B$16)-SEARCH(" ",B$16)),$C$2:$C$13,LEFT(B$16,1)) C19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(C$17,1),$D$2:$D$13,RIGHT(C$16,LEN(C$16)-SEARCH(" ",C$16)),$C$2:$C$13,LEFT(C$16,1)) D19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(D$17,1),$D$2:$D$13,RIGHT(D$16,LEN(D$16)-SEARCH(" ",D$16)),$C$2:$C$13,LEFT(D$16,1)) E19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(E$17,1),$D$2:$D$13,RIGHT(E$16,LEN(E$16)-SEARCH(" ",E$16)),$C$2:$C$13,LEFT(E$16,1)) F19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(F$17,1),$D$2:$D$13,RIGHT(F$16,LEN(F$16)-SEARCH(" ",F$16)),$C$2:$C$13,LEFT(F$16,1)) G19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(G$17,1),$D$2:$D$13,RIGHT(F$16,LEN(F$16)-SEARCH(" ",F$16)),$C$2:$C$13,LEFT(F$16,1)) H19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(H$17,1),$D$2:$D$13,RIGHT(H$16,LEN(H$16)-SEARCH(" ",H$16)),$C$2:$C$13,LEFT(H$16,1)) I19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(I$17,1),$D$2:$D$13,RIGHT(H$16,LEN(H$16)-SEARCH(" ",H$16)),$C$2:$C$13,LEFT(H$16,1)) J19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(J$17,1),$D$2:$D$13,RIGHT(J$16,LEN(J$16)-SEARCH(" ",J$16)),$C$2:$C$13,LEFT(J$16,1)) K19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(K$17,1),$D$2:$D$13,RIGHT(J$16,LEN(J$16)-SEARCH(" ",J$16)),$C$2:$C$13,LEFT(J$16,1)) L19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(L$17,1),$D$2:$D$13,RIGHT(L$16,LEN(L$16)-SEARCH(" ",L$16)),$C$2:$C$13,LEFT(L$16,1)) M19 =COUNTIFS($A$2:$A$13,$A19,$E$2:$E$13,LEFT(M$17,1),$D$2:$D$13,RIGHT(L$16,LEN(L$16)-SEARCH(" ",L$16)),$C$2:$C$13,LEFT(L$16,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>