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]

 

[Domenic]

Try...

A2:E5

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Nate[/TD]
[TD="class: xl63, width: 64"]FTM01[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Nate[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Nate[/TD]
[TD="class: xl63"]FTM01[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Vito[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]Vito[/TD]
[TD="class: xl63"]BD02[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Nate[/TD]
[TD="class: xl63"]SM02[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF($A$2:$A$5=D2,IF(LEN($B$2:$B$5)>0,MATCH($B$2:$B$5,$B$2:$B$5,0))),ROW($B$2:$B$5)-ROW($B$2)+1)>0,1))

Hope this helps!

 

[mcbiz77]

Thanks Domenic for the information! I tried adding additional columns between the Name (column A) and Sheet (column B) and when I do the calculation shows 0 rather than 2 and 1. Do you know what i have to do to the formula so that it calculates it correctly?

 

[mcbiz77]

Domenic, I was able to get the formula to work with the additional columns between the Name and Sheet however I notice that if I have more than 21 Names, the formula spits out an answer of #NA. Do you know why i'm getting this?

 

[Aladin Akyurek]

Domenic, I was able to get the formula to work with the additional columns between the Name and Sheet however I notice that if I have more than 21 Names, the formula spits out an answer of #NA. Do you know why i'm getting this?

Care to post the formula as you implemented it?

 

[mcbiz77]

This is the formula that I'm using. It's actually anything over 22 is when I get the #N/A error.

=SUM(IF(FREQUENCY(IF(A4:A22=BJ4,IF(LEN(E4:E22)>0,MATCH(E4:E22,E4:E22))),ROW(E4:E22)-ROW(E4)+1)>0,1))

 

[Aladin Akyurek]

This is the formula that I'm using. It's actually anything over 22 is when I get the #N/A error.

=SUM(IF(FREQUENCY(IF(A4:A22=BJ4,IF(LEN(E4:E22)>0,MATCH(E4:E22,E4:E22))),ROW(E4:E22)-ROW(E4)+1)>0,1))

The MATCH bit is incomplete... Control+shift+enter:

=SUM(IF(FREQUENCY(IF($A$4:$A$22=BJ4,IF(LEN($E$4:$E$22)>0,
    MATCH($E$4:$E$22,$E$4:$E$22,0))),ROW($E$4:$E$22)-ROW($E$4)+1)>0,1))

 

[mcbiz77]

Thank you very much Aladin! My formula finally works. How I left that part out beats me but it also looks like Domenic had that in their equation too.

 

[mcbiz77]

Another Question: Now that I have the total number of sheets per person, now I want to know if they comply or not. For example: Nate has a total number of 2 sheets (duplicate sheet removed) however he has 1 sheet that is not compliant (NO). What formula would I use to calculate the number not compliant?

A B C
Nate FTM01 Yes
Nate FTM01 No
Vito BD02 Yes
Nate SM02 Yes

 

[Aladin Akyurek]

Another Question: Now that I have the total number of sheets per person, now I want to know if they comply or not. For example: Nate has a total number of 2 sheets (duplicate sheet removed) however he has 1 sheet that is not compliant (NO). What formula would I use to calculate the number not compliant?

A B C
Nate FTM01 Yes
Nate FTM01 No
Vito BD02 Yes
Nate SM02 Yes

What is the result for the exhibit above?