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>

 

[Domenic]

Try...

A2:E5

Nate	FTM01		Nate	2
Nate	FTM01		Vito	1
Vito	BD02
Nate	SM02

<tbody>
</tbody>

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?