Extracting 2nd, 3rd (and so on) Values while ignoring Duplicates

[LearnExcl]

Hello All, I need your assistance. See the tables below. So, I need to extract a unique list of vendor codes from the "DATA" below based on their area codes. Then, I need to sum the values under "Presence" corresponding to the vendor codes. The cell (B1) next to "Area Code" below is where I would enter the area code for which I would like to extract a unique list of vendor codes as well as their presence.The result of the area code SADF is illustrated below. I very much appreciate your time and effort in helping me out.

DATA Sheet1 A B C
Area Code	Vendor Code	Presence
SADF	1F090	1
SADF	1F090	0
SADF	1F090	1
SADF	2N031	1
SADF	9D001	1
SADF	2N031	1
SADF	9D001	0
SADF	1F090	1
FADR	1F090	1
FADR	1F090	1
FADR	4R023	1
FADR	1F090	0
CAER	4R023	1
CAER	5TA90	1
CAER	5TA90	0
CAER	2S0TY	1

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

RESULT Sheet 2 A B
Area Code:	SADF
Vendor Code	Presence
1F090	3
2N031	2
9D001	1

<tbody>
</tbody><colgroup><col span="2"></colgroup>

 

[Weazel]

maybe something like....

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Area Code	Vendor Code	Presence		Area Code:	SADF
2	SADF	1F090	1		Vendor Code	Presence
3	SADF	1F090	0		1F090	3
4	SADF	1F090	1		2N031	2
5	SADF	2N031	1		9D001	1
6	SADF	9D001	1
7	SADF	2N031	1
8	SADF	9D001	0
9	SADF	1F090	1
10	FADR	1F090	1
11	FADR	1F090	1
12	FADR	4R023	1
13	FADR	1F090	0
14	CAER	4R023	1
15	CAER	5TA90	1
16	CAER	5TA90	0
17	CAER	2S0TY	1

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F3 =IF(E3="","",SUMIFS(Sheet1!$C$2:$C$17,Sheet1!$A$2:$A$17,$F$1,Sheet1!$B$2:$B$17,E3)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E3 {=IFERROR(INDEX(Sheet1!$B$2:$B$17,MATCH(0,IF(Sheet1!$A$2:$A$17=$F$1,COUNTIF($E$2:E2,Sheet1!$B$2:$B$17)),0)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[LearnExcl]

maybe something like....

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Area Code	Vendor Code	Presence		Area Code:	SADF
2	SADF	1F090	1		Vendor Code	Presence
3	SADF	1F090			1F090	3
4	SADF	1F090	1		2N031	2
5	SADF	2N031	1		9D001	1
6	SADF	9D001	1
7	SADF	2N031	1
8	SADF	9D001
9	SADF	1F090	1
10	FADR	1F090	1
11	FADR	1F090	1
12	FADR	4R023	1
13	FADR	1F090
14	CAER	4R023	1
15	CAER	5TA90	1
16	CAER	5TA90
17	CAER	2S0TY	1

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F3 =IF(E3="","",SUMIFS(Sheet1!$C$2:$C$17,Sheet1!$A$2:$A$17,$F$1,Sheet1!$B$2:$B$17,E3)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E3 {=IFERROR(INDEX(Sheet1!$B$2:$B$17,MATCH(0,IF(Sheet1!$A$2:$A$17=$F$1,COUNTIF($E$2:E2,Sheet1!$B$2:$B$17)),0)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you so much for your response. I need an additional huge favor though. In addition to the Area Code, if I wanted to control for another criterion, how would I go about doing that? For instance, under column D, there is a list of values reflecting access codes (ranging from 101 to 106). Can we insert "Access Code" in G1 and then enter 101 - 106 in cells G2 - G7 and control for them that way? Keep in mind, there may be a need to control for only 1 or up to 6 access codes at a time. Your help will be greatly appreciated.

 

[LearnExcl]

Hi Weasel. Would you be so kind as to explain the MATCH(0,IF(Sheet1!$A$2:$A$17=$F$1,COUNTIF($E$2:E2,Sheet1!$B$2:$B$17)),0) portion of the formula? I really appreciate your time.