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.

[TABLE="width: 213"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Nate
[/TD]
[TD]FTM01[/TD]
[/TR]
[TR]
[TD]Nate[/TD]
[TD]FTM01[/TD]
[/TR]
[TR]
[TD]Vito[/TD]
[TD]BD02[/TD]
[/TR]
[TR]
[TD]Nate[/TD]
[TD]SM02[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]name[/td][td]sheet[/td][td]yes/no[/td][/tr]


[tr][td]

2​

[/td][td]Nate[/td][td]FTM01[/td][td]Yes[/td][/tr]


[tr][td]

3​

[/td][td]Nate[/td][td]FTM01[/td][td]No[/td][/tr]


[tr][td]

4​

[/td][td]Vito[/td][td]BD02[/td][td]Yes[/td][/tr]


[tr][td]

5​

[/td][td]Nate[/td][td]SM02[/td][td]Yes[/td][/tr]
[/table]

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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]name[/td][td]sheet[/td][td]yes/no[/td][td][/td][td]Nate[/td][td]

1​

[/td][/tr]


[tr][td]

2​

[/td][td]Nate[/td][td]FTM01[/td][td]Yes[/td][td][/td][td]Vito[/td][td]

0​

[/td][/tr]


[tr][td]

3​

[/td][td]Nate[/td][td]FTM01[/td][td]No[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]Vito[/td][td]BD02[/td][td]Yes[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]Nate[/td][td]SM02[/td][td]Yes[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.