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
PAD	WELL	rd	targ	dir
34H	d	Middle	s
34H	d	Lower	n
34H	r	Upper	n
34H	r	Middle	n
34H	d	Upper	n
81G	d	Upper	n
81G	d	Lower	n
81G	d	Middle	n
81G	d	Middle	n
81G	d	Middle	n
81G	r	Middle	s
81G	r	Upper	s
north	south	north	south	north	south	north	south	north	south	north	south
34h
81g

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: center"]drilled upper[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Drilled middle[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Drilled lower[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Remaining Upper[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Remaining Middle[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Remaining lower[/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet35​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M18[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M19[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]