Count text formula based on multi-criteria excluding duplicates

[mcbiz77]

Hello,
I'm looking for a count formula that counts the total number of sheets per person excluding duplicates. For example, below Nate has FTM01 and SM02 sheets assigned to him. His name is shown as 3 times in the table but the sheet names are the same for two of the items below. The answer needs to show that Nate has 2 sheets and Vito has 1 sheet. Please help me in finding a formula.

A B Nate FTM01 Nate FTM01 Vito BD02 Nate SM02 <tbody> </tbody>

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

 

[mcbiz77]

The formula should spit out the following result

Nate - 1 (meaning he has one process sheet that has NO)
Vito - 1 (meaning he has one process sheet that has NO)

**** the most important thing is that even though Nate has 3 sheets, 2 of them are duplicate sheets. So, if the first two rows had a No and No then it would need to count that as 1 NO since they are duplicate sheets.

 

[Aladin Akyurek]

The formula should spit out the following result

Nate - 1 (meaning he has one process sheet that has NO)
Vito - 1 (meaning he has one process sheet that has NO)

**** the most important thing is that even though Nate has 3 sheets, 2 of them are duplicate sheets. So, if the first two rows had a No and No then it would need to count that as 1 NO since they are duplicate sheets.

You posted...

Row\Col	A​	B​	C​
1​	name	sheet	yes/no
2​	Nate	FTM01	Yes
3​	Nate	FTM01	No
4​	Vito	BD02	Yes
5​	Nate	SM02	Yes

with headers added.

How do we get Nate >> 1 and Vito >> 1, using the above exhibit?

 

[mcbiz77]

Aladin thanks for generating the table above. Nate gets 1 because he has (1) "No" which is against sheet FTM01. If both rows 2 and 3 were "No" then Nate would still have (1) "No" since the sheets are the same (FTM01). As for Vito, he would get 0 since he has 0 "No". I was incorrect above by saying Vito gets 1. Sorry for the confusion.

 

[Aladin Akyurek]

Aladin thanks for generating the table above. Nate gets 1 because he has (1) "No" which is against sheet FTM01. If both rows 2 and 3 were "No" then Nate would still have (1) "No" since the sheets are the same (FTM01). As for Vito, he would get 0 since he has 0 "No". I was incorrect above by saying Vito gets 1. Sorry for the confusion.

Ok.

Row\Col	A​	B​	C​	D​	E​	F​
1​	name	sheet	yes/no		Nate	1​
2​	Nate	FTM01	Yes		Vito	0​
3​	Nate	FTM01	No
4​	Vito	BD02	Yes
5​	Nate	SM02	Yes

In F1 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$5=E1,IF(1-($B$2:$B$5=""),IF($C$2:$C$5="No",
    MATCH($B$2:$B$5,$B$2:$B$5,0)))),ROW($B$2:$B$5)-ROW($B$2)+1),1))

 

[mcbiz77]

Thank you Aladin for your help

 

[Aladin Akyurek]

Thank you Aladin for your help

You are welcome.