Counting unique values

[HardBeatZ]

Hello everyone!

I have a list of clients who visit different locations on different days. I need to count the number of unique clients that are visiting each center on the various days.

The client data is sensitive so I am unable to share the details however below is how the data is put together


Date First Name Last Name Center

01/04/18 John Doe Center 12
01/15/18 Jane Doe Center 11

I then have a table built that lists each center in column A with a cell that allows me to switch the date and am trying to get the number of unique clients in cell B.

I tried using a sum if using frequency, but I keep ending up off by 1, I figured this was due to formulas being written in cells that did not contain data yet so I had it subtract one from every center, but then two of my centers were off by the other way.

I know I can use countifs to come up with a solution, but my understanding is that countifs can be data extensive when using large amounts of data and this could be data that goes up to about 100,000 rows.

I'm clearly missing something just don't know what it is.

 

[Coding4Fun]

I am not sure how you have your cell that allows you to switch the date set up so I am not sure how you would tie this into that but you can use this.

I set up a sheet as mentioned with the columns (Date / First Name /Last Name / Center ) - Columns A, B, C, D

I then put in sample data


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Center[/TD]
[/TR]
[TR]
[TD]01/04/2018[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Center 12[/TD]
[/TR]
[TR]
[TD]01/15/2018[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Center 11[/TD]
[/TR]
[TR]
[TD]01/15/2018[/TD]
[TD]James[/TD]
[TD]Doe[/TD]
[TD]Center 5[/TD]
[/TR]
[TR]
[TD]01/20/2018[/TD]
[TD]Jill[/TD]
[TD]Doe[/TD]
[TD]Center12[/TD]
[/TR]
[TR]
[TD]01/20/2018[/TD]
[TD]Jack[/TD]
[TD]Doe[/TD]
[TD]Center 5[/TD]
[/TR]
[TR]
[TD]01/20/2018[/TD]
[TD]Joe[/TD]
[TD]Doe[/TD]
[TD]Center 6[/TD]
[/TR]
[TR]
[TD]01/20/2018[/TD]
[TD]Janna[/TD]
[TD]Doe[/TD]
[TD]Center 5[/TD]
[/TR]
[TR]
[TD]01/23/2018[/TD]
[TD]Jon[/TD]
[TD]Doe[/TD]
[TD]Center 6[/TD]
[/TR]
[TR]
[TD]01/23/2018[/TD]
[TD]Jim[/TD]
[TD]Doe[/TD]
[TD]Center 12[/TD]
[/TR]
[TR]
[TD]01/23/2018[/TD]
[TD]Jenny[/TD]
[TD]Doe[/TD]
[TD]Center 11[/TD]
[/TR]
[TR]
[TD]01/24/2018[/TD]
[TD]JoAnn[/TD]
[TD]Doe[/TD]
[TD]Center 12[/TD]
[/TR]
[TR]
[TD]01/24/2018[/TD]
[TD]Jenna[/TD]
[TD]Doe[/TD]
[TD]Center 11[/TD]
[/TR]
[TR]
[TD]01/24/2018[/TD]
[TD]Jamie[/TD]
[TD]Doe[/TD]
[TD]Center 11[/TD]
[/TR]
</tbody>[/TABLE]


Then in the same sheet I entered off to the right, I put a date in Cell I1 - This is where it pulls the date for use in our formula. You would change the date in Cell I1 to change the counts in you list of Centers.

With the date in Cell I1 I then Create A list of Centers on the same sheet, again this is just for demonstration, you will need to adjust for your use. I put my list of centers on the same sheet in Column Q.

Next to the center list in column R i enter the formula

=COUNTIFS(D:D,Q2,A:A,$I$1)

And then copy this down through the list of Centers. Note the locked cells in the formula ($)

 

[AhoyNC]

Maybe something like this:
You are right that COUNTIFS can slow things down when you have large amounts of data.
I'm assuming you have no blank cells in your data. If you do we may have to make a few changes.

This is an array formula and must be entered with CTRL-SHIFT-ENTER
Drag formula down as needed.

Excel Workbook
	A	B	C	D
1	Date	First Name	Last Name	Center
2	1/4/2018	John	Doe	Center 12
3	1/20/2018	Jane	Doe	Center 11
4	1/20/2018	Jane	Doe	Center 11
5	1/20/2018	Jill	Doe	Center 12
6	1/20/2018	Jack	Doe	Center 5
7	1/20/2018	Joe	Doe	Center 6
8	1/20/2018	Janna	Doe	Center 5
9	1/23/2018	Jon	Doe	Center 6
10	1/23/2018	Jim	Doe	Center 12
11	1/23/2018	Jenny	Doe	Center 11
12	1/24/2018	JoAnn	Doe	Center 12
13	1/24/2018	Jenna	Doe	Center 11
14	1/24/2018	Jamie	Doe	Center 11
15
16
17	Date	1/20/2018
18	Center	Unique Count
19	Center 5	2
20	Center 6	1
21	Center 11	1
22	Center 12	1
Sheet