Count unique items based on multiple criteria and making it easily reproducible

Hello everyone! I just registered and I need some help counting unique values based on multiple criteria. Currently I am using a different program to do this calculation because counting unique values is something that it does very easily and quickly. The problem is that I am using the data in a number of different ways in Access and Excel and I need to be able to reproduce the count on another sheet (in the same file where I am using the data for other things) when the data table is refreshed.

I've done quite a bit of searching but I think I've just confused myself and I'm not sure what the best way to do this would be.

Here's the scenario that I am working with: I am trying to count unique persons associated with a certain program in a certain "area". There are multiple values for each of these different fields and I want to be able to associate unique persons with 1 program and 1 "area". (Programs and "areas" can be mixed and matched as they could be associated with any quite a few different combinations of these")

Sample data layout:

person_____program_____area
Person 1___Program 1___Area AA
Person 1___Program 1___Area AB
Person 2___Program 2___Area CD
Person 3___Program 3___Area CE
Person 4___Program 4___Area ME
Person 4___Program 4___Area EI
Person 4___Program 4___Area LK
Person 5___Program 5___Area PO
Person 6___Program 6___Area TR
Person 7___Program 7___Area EE
Person 8___Program 8___Area QW

Although it looks like we can assume that the same person will always be associated with a specific program, it could change and I would want to identify them uniquely in each program.

Would it be better to create a concatenation of these three fields and then count uniques? I just don't know, I've come up with a few different ways but they have been labor intensive and I can't reproduce them easily.

Thanks!
Clint

Hello Clint,

So in your example all of the rows are different so the "unique count" would give 11?

Concatenation would probably be the simplest way, using this formula in D2 copied down

=A2&"-"&B2&"-"&C2

Then for a unique count

=SUMPRODUCT((D2:D1000<>"")/COUNTIF(D2:D1000,D2:D1000&""))

Assuming that A2:C12 contains the data, E2 contains the program of interest, and F2 contains the area of interest, maybe...

=SUM(IF(FREQUENCY(IF($B$2:$B$12=E2,IF($C$2:$C$12=F2,IF($A$2:$A$12<>"",MATCH("~"&$A$2:$A$12,$A$2:$A$12&"",0)))),ROW($A$2:$A$12)-ROW($A$2)+1),1))

...which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

xl-central.com

Originally Posted by NBVC

Hey Domenic,

Neat little website (library) you got going there... I have bookmarked it!

Hopefully it grows very large.

Thanks NBVC, I really appreciate it!

Need some MMULT(...TRANSPOSE)) formulas.... those are the ones I really want to learn how to manage

Thanks.

Wow, my first request! I'll definitely have to include them.

xl-central.com

Shg, DonkeyOte,

Thanks guys, I really appreciate the vote of confidence!

Cheers!

xl-central.com

After looking at your responses and thinking about it some more, I may need to go a slightly different route. What if I were to concatenate the three distinguishing fields and associate an identifier (X or 1) with a unique item (It would need to look at the entire column to see if there is a duplicate and only put the identifier on one of the similar values). Is there a way that I can do this? I think it would allow me to pivot based on person, program or area to get a count...

Thanks for the help!

Originally Posted by phate1985

After looking at your responses and thinking about it some more, I may need to go a slightly different route. What if I were to concatenate the three distinguishing fields and associate an identifier (X or 1) with a unique item (It would need to look at the entire column to see if there is a duplicate and only put the identifier on one of the similar values). Is there a way that I can do this? I think it would allow me to pivot based on person, program or area to get a count...

Thanks for the help!

Unclear... Daddylonglegs and I have provided solutions which I think return different results. Which one returns the desired result? A few examples based on some sample data would help.

xl-central.com

Originally Posted by Domenic

Unclear... Daddylonglegs and I have provided solutions which I think return different results. Which one returns the desired result? A few examples based on some sample data would help.

xl-central.com

Sorry about that but I have come up with a solution, albeit through Access and my original query that is in a different program.

Here is my solution:

I now have another column in my original query that concatenates the fields that I need (person-program-area). I then use Access (which I was using to compare the different data from the original query anyway) to make a new table, "group by" the concatenated field, so that I am only bringing in one row per unique concatenated field. From there, I have Excel linked (which I did anyway) and I am pivoting the data and using the concatenation for my "values" (counting them). This allows me to see uniques associated with with a certain area and program

I apologize if my original explanation was a bit unclear but this seems to be giving me the results that I need!

Thanks for the help! Although I didn't use some of the advice given for this problem, I think that I can use it to address some other things that I am working with.

-Clint