Counting unique text values based on criteria

[macrorookie]

Hello.

I created a super-simple spreadsheet with two tabs to highlight my challenge. I have a source sheet and a destination sheet. The source sheet shows status groups, with various people's names next to each line within each group. On the destination sheet (where I need the calculation to happen), I have a summary where I want to show the number of unique names for each group. The destination sheet doesn't have details of the names -- simply a number showing how many unique names show for each group. Any thoughts?

Thanks in advance.

[hemesh]

copy paste below in b6 and enter
=SUM(IF(FREQUENCY(MATCH(INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)):INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)+COUNTIF(Source!$A$6:$A$26,Destination!$A5)-1),INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)):INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)+COUNTIF(Source!$A$6:$A$26,Destination!$A5)-1),0),MATCH(INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)):INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)+COUNTIF(Source!$A$6:$A$26,Destination!$A5)-1),INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)):INDEX(Source!$B$6:$B$26,MATCH(Destination!$A5,Source!$A$6:$A$26,0)+COUNTIF(Source!$A$6:$A$26,Destination!$A5)-1),0))>=1,1,0))

[dluhut]

Here you go.

Please note that formula is in ARRAY.

[MarvinP]

Hi,

This looks like a Pivot Table problem to me. See the attached where I've done it using a PT. No formulas needed.

[dluhut]

Hi,

This looks like a Pivot Table problem to me. See the attached where I've done it using a PT. No formulas needed.

Hey Marvin,

At first I thought of using Pivot Tables too...but I just can't get the "UNIQUE" count under each Loan status

[MarvinP]

Try this one with some helper columns then.

[macrorookie]

Gang, thank you so much for the help! the three of you have collectively solved my problem, and it's greatly appreciated. I'll make sure to mark this as solved, and add to your "reputations" through the star button. Thanks again!!