Stumped on counting patients unique visits to a clinic

[realtoast]

I have a large database of patient visits to a single health clinic. I need to be able to create a dynamic list of number unique SubscriberID visits the clinic in a year.But in my data base, a single visit can have multiple claims instances (exam, consultation, lab, etc.). All my attempts have the same problem: If a SubscriberID went to the clinic on 4/30/2020, and had three procedures, my list counts three encounters. I'm need the list to show he just went to the clinic one time on 4/30/2020.

Please don't bother copy/pasting AI solutions. I've spent three days with that method.

In the below instance, the SubscriberID, aaabawavd, visited the clinic on 4/30/2020, and had three separate claims on that day. I want to have a dynamic spill array that counts how many unique visits where at the clinic in a current year. All my formulas or pivots are incorrect, show that aaabawavd had three encounters on 4/30/2020. I need to show he had only one encounter on 4/30/2020. After all, he visited the clinic just once that day, not three times.

I hope this makes sense.

Countif Problem.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Year	Subscriber ID	CCM vs Employee	ClaimNumber	Plan	Group	Relationship	Date of Service	Paid Date	Product Name
2	2020	aaabawavd	Employee	220K9385600	100H201250	PPO	Employee	4/30/20	05/27/2020	Medical
3	2020	aaabawavd	Employee	220K9385601	100H201250	PPO	Employee	4/30/20	07/01/2020	Medical
4	2020	aaabawavd	Employee	220K9385602	100H201250	PPO	Employee	4/30/20	07/01/2020	Medical
5	2021	aaabawavd	Employee	221LQ778700	100H201250	PPO	Employee	10/7/21	11/10/2021	Medical
6	2021	aaabawavd	Employee	221V4183600	100H201250	PPO	Employee	6/8/21	07/07/2021	Medical
7	2022	aaabawavd	Employee	222Q1977200	100H201250	PPO	Employee	4/7/22	05/04/2022	Medical
8	2022	aaabawavd	Employee	222SH650000	100H201250	PPO	Employee	9/22/22	10/19/2022	Medical
9	2023	aaabawavd	Employee	22366389300	100H201250	PPO	Employee	1/26/23	02/15/2023	Medical
10	2020	aaabawved	Employee	220LR303500	100H20HD	HDHP	Employee	11/19/20	12/22/2020	Medical
11	2020	aaabawved	Employee	220P7422100	100H20HD	HDHP	Employee	6/8/20	07/01/2020	Medical
12	2020	aaabawved	Employee	220S5842400	100H20HD	HDHP	Employee	6/26/20	07/22/2020	Medical
13	2021	aaabawved	Employee	220MW565500	100H20HD	HDHP	Employee	12/28/20	01/13/2021	Medical
14	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	10/21/2020	Medical
15	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	10/21/2020	Medical
16	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	10/21/2020	Medical
17	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	10/21/2020	Medical
18	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	10/21/2020	Medical
19	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	10/28/2020	Medical
20	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	10/28/2020	Medical
21	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	10/28/2020	Medical
22	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	10/28/2020	Medical
23	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/9/20	10/28/2020	Medical
24	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/9/20	10/28/2020	Medical
25	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/9/20	10/28/2020	Medical
26	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/9/20	11/04/2020	Medical
27	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/9/20	11/04/2020	Medical
28	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/9/20	11/04/2020	Medical
29	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/9/20	11/04/2020	Medical
30	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/9/20	11/04/2020	Medical
31	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	04/27/2022	Medical
32	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	04/27/2022	Medical
33	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	04/27/2022	Medical
34	2022	aabzcdevx	Employee	222BSS65300	100H201250	PPO	Dependent	11/1/22	12/20/2022	Medical
35	2022	aabzcdevx	Employee	222BSS65300	100H201250	PPO	Dependent	11/1/22	12/20/2022	Medical
36	2023	aabzcdevx	Employee	22388578300	100H201250	PPO	Dependent	1/27/23	03/01/2023	Medical
37	2023	aabzcdevx	Employee	223X6485400	100H201250	PPO	Dependent	5/2/23	05/24/2023	Medical
38	2023	aabzcdevx	Employee	223X6485400	100H201250	PPO	Dependent	5/2/23	05/24/2023	Medical
39	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	02/19/2020	Medical
40	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	02/19/2020	Medical
41	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	02/19/2020	Medical
42	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	02/19/2020	Medical
43	2020	aadxbwzvx	Employee	22088710200	100H20HD	HDHP	Employee	1/28/20	03/18/2020	Medical
44	2020	aavzxxvcx	CCM	219MF340100	100H20CCM	CCM	Employee	10/31/19	01/29/2020	Medical
45	2020	aavzxxvcx	CCM	219MK056800	100H20CCM	CCM	Spouse	12/10/19	02/12/2020	Medical
46	2020	aavzxxvcx	CCM	219MK061700	100H20CCM	CCM	Spouse	12/17/19	02/12/2020	Medical
47	2020	aavzxxvcx	CCM	219MK664000	100H20CCM	CCM	Employee	12/19/19	02/19/2020	Medical
48	2020	aavzxxvcx	CCM	22040453700	100H20CCM	CCM	Spouse	1/10/20	02/12/2020	Medical
49	2020	aavzxxvcx	CCM	220D7276400	100H20CCM	CCM	Spouse	3/19/20	04/15/2020	Medical
50	2020	aavzxxvcx	CCM	220DD993100	100H20CCM	CCM	Spouse	8/24/20	09/30/2020	Medical
51	2020	aavzxxvcx	CCM	220GR322300	100H20CCM	CCM	Spouse	10/5/20	10/28/2020	Medical
52	2020	aavzxxvcx	CCM	220KC170400	100H20CCM	CCM	Spouse	11/2/20	12/02/2020	Medical
53	2020	aavzxxvcx	CCM	220KC170400	100H20CCM	CCM	Spouse	11/2/20	12/02/2020	Medical
54	2022	aavzxxvcx	CCM	222B7031500	100H20CCM	CCM	Spouse	2/17/22	03/09/2022	Medical
55	2022	aavzxxvcx	CCM	222BD369500	100H20CCM	CCM	Spouse	3/23/22	06/22/2022	Medical
56	2022	aavzxxvcx	CCM	222BD369500	100H20CCM	CCM	Spouse	3/23/22	06/22/2022	Medical
57	2022	aavzxxvcx	CCM	221MX083700	100H20CCM	CCM	Spouse	10/29/21	03/02/2022	Medical
58	2022	aavzxxvcx	CCM	22246583900	100H20CCM	CCM	Spouse	1/19/22	03/09/2022	Medical
59	2022	aavzxxvcx	CCM	22252427300	100H20CCM	CCM	Employee	1/24/22	02/16/2022	Medical
60	2022	aavzxxvcx	CCM	222J4889000	100H20CCM	CCM	Employee	2/9/22	04/06/2022	Medical
61	2022	aavzxxvcx	CCM	222BLP78200	100H20CCM	CCM	Spouse	11/7/22	12/14/2022	Medical
62	2023	aavzxxvcx	CCM	223BT845700	100H20CCM	CCM	Spouse	5/22/23	06/07/2023	Medical
63	2023	aavzxxvcx	CCM	223GV946200	100H20CCM	CCM	Spouse	6/26/23	07/19/2023	Medical
64	2021	aawydzcyx	Employee	221W5640500	100H201250	PPO	Employee	6/9/21	07/28/2021	Medical
65	2022	aawydzcyx	Employee	222L8458100	100H201250	PPO	Employee	3/9/22	04/20/2022	Medical
66	2021	aaxdbydbd	Employee	221M4705300	100H20HD	HDHP	Employee	4/29/21	05/26/2021	Medical
67	2022	aaxdbydbd	Employee	222L8473200	100H20HD	HDHP	Employee	3/21/22	04/20/2022	Medical
68	2020	aaxxdxczx	Employee	220FH704800	100H201250	PPO	Employee	9/15/20	10/28/2020	Medical
69	2021	aaxxdxczx	Employee	221PN490100	100H201250	PPO	Employee	11/23/21	12/08/2021	Medical
70	2022	aaxxdxczx	Employee	22296695700	100H201250	PPO	Employee	2/15/22	03/09/2022	Medical
71	2022	aaxxdxczx	Employee	22296695700	100H201250	PPO	Employee	2/15/22	03/09/2022	Medical
72	2022	aaxxdxczx	Employee	222LH006400	100H201250	PPO	Employee	8/15/22	08/31/2022	Medical
73	2022	aaxxdxczx	Employee	222V4066100	100H201250	PPO	Employee	5/5/22	05/25/2022	Medical
74	2022	aaxxdxczx	Employee	222Z2119000	100H201250	PPO	Employee	5/16/22	06/08/2022	Medical
75	2020	aazczdxad	Employee	220KT781700	100H201250	PPO	Spouse	11/9/20	12/16/2020	Medical
76	2020	aazczdxad	Employee	220V5448800	100H201250	PPO	Employee	7/8/20	08/05/2020	Medical
77	2020	aazczdxad	Employee	220X2205600	100H201250	PPO	Spouse	7/22/20	10/21/2020	Medical
78	2020	aazczdxad	Employee	220D6282800	100H20HD	HDHP	Employee	3/17/20	04/15/2020	Medical
79	2021	aazczdxad	Employee	22116547300	100H201250	PPO	Spouse	1/11/21	02/03/2021	Medical
80	2021	aazczdxad	Employee	221CC450400	100H201250	PPO	Employee	7/19/21	08/11/2021	Medical
81	2021	aazczdxad	Employee	221CX613400	100H201250	PPO	Employee	8/2/21	08/25/2021	Medical
82	2021	aazczdxad	Employee	221DT743800	100H201250	PPO	Spouse	8/10/21	09/01/2021	Medical
83	2021	aazczdxad	Employee	221F5551800	100H201250	PPO	Spouse	3/18/21	04/21/2021	Medical
84	2022	aazczdxad	Employee	222FT169700	100H201250	PPO	Employee	7/12/22	07/27/2022	Medical
85	2022	aazczdxad	Employee	222HH944100	100H201250	PPO	Employee	7/18/22	08/03/2022	Medical
86	2022	aazczdxad	Employee	222WD169600	100H201250	PPO	Employee	10/26/22	11/16/2022	Medical
87	2022	aazczdxad	Employee	222WD169600	100H201250	PPO	Employee	10/26/22	11/16/2022	Medical
88	2023	aazczdxad	Employee	223C8624600	100H201250	PPO	Employee	2/6/23	03/15/2023	Medical
Sheet1

 

[arthurbr]

Hi which XL version are you using, please? Your profile does not say. Thx ( newer versions have newer functions)

 

[realtoast]

Thank you. I’m using 365

 

[arthurbr]

Perhaps

=ROWS(UNIQUE(FILTER(B2:B100,B2:B100<>"")))

Please update your profile, it will be easier for the future. Thx

 

[JEC]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Subscriber ID	CCM vs Employee	ClaimNumber	Plan	Group	Relationship	Date of Service	Paid Date	Product Name			Name	Year	Count
2	2020	aaabawavd	Employee	220K9385600	100H201250	PPO	Employee	4/30/20	43978	Medical			aaabawavd	2020	1
3	2020	aaabawavd	Employee	220K9385601	100H201250	PPO	Employee	4/30/20	44013	Medical
4	2020	aaabawavd	Employee	220K9385602	100H201250	PPO	Employee	4/30/20	44013	Medical
5	2021	aaabawavd	Employee	221LQ778700	100H201250	PPO	Employee	10-7-2021	44510	Medical
6	2021	aaabawavd	Employee	221V4183600	100H201250	PPO	Employee	6-8-2021	44384	Medical
7	2022	aaabawavd	Employee	222Q1977200	100H201250	PPO	Employee	4-7-2022	44685	Medical
8	2022	aaabawavd	Employee	222SH650000	100H201250	PPO	Employee	9/22/22	44853	Medical
9	2023	aaabawavd	Employee	22366389300	100H201250	PPO	Employee	1/26/23	44972	Medical
10	2020	aaabawved	Employee	220LR303500	100H20HD	HDHP	Employee	11/19/20	44187	Medical
11	2020	aaabawved	Employee	220P7422100	100H20HD	HDHP	Employee	6-8-2020	44013	Medical
12	2020	aaabawved	Employee	220S5842400	100H20HD	HDHP	Employee	6/26/20	44034	Medical
13	2021	aaabawved	Employee	220MW565500	100H20HD	HDHP	Employee	12/28/20	44209	Medical
14	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
15	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
16	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
Sheet1

Cell Formulas
Range	Formula
O2	O2	=ROWS(UNIQUE(FILTER(H2:H88,(B2:B88=M2)*(A2:A88=N2))))

 

[Fluff]

Thank you. I’m using 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year	Subscriber ID	CCM vs Employee	ClaimNumber	Plan	Group	Relationship	Date of Service	Paid Date	Product Name
2	2020	aaabawavd	Employee	220K9385600	100H201250	PPO	Employee	4/30/20	43978	Medical		2020	7
3	2020	aaabawavd	Employee	220K9385601	100H201250	PPO	Employee	4/30/20	44013	Medical		2021	6
4	2020	aaabawavd	Employee	220K9385602	100H201250	PPO	Employee	4/30/20	44013	Medical		2022	7
5	2021	aaabawavd	Employee	221LQ778700	100H201250	PPO	Employee	10/07/2021	44510	Medical		2023	4
6	2021	aaabawavd	Employee	221V4183600	100H201250	PPO	Employee	06/08/2021	44384	Medical
7	2022	aaabawavd	Employee	222Q1977200	100H201250	PPO	Employee	04/07/2022	44685	Medical
8	2022	aaabawavd	Employee	222SH650000	100H201250	PPO	Employee	9/22/22	44853	Medical
9	2023	aaabawavd	Employee	22366389300	100H201250	PPO	Employee	1/26/23	44972	Medical
10	2020	aaabawved	Employee	220LR303500	100H20HD	HDHP	Employee	11/19/20	44187	Medical
11	2020	aaabawved	Employee	220P7422100	100H20HD	HDHP	Employee	06/08/2020	44013	Medical
12	2020	aaabawved	Employee	220S5842400	100H20HD	HDHP	Employee	6/26/20	44034	Medical
13	2021	aaabawved	Employee	220MW565500	100H20HD	HDHP	Employee	12/28/20	44209	Medical
14	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
15	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
16	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
17	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
18	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
19	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
20	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
21	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
22	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
23	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/09/2020	44132	Medical
24	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/09/2020	44132	Medical
25	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/09/2020	44132	Medical
26	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
27	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
28	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
29	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
30	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
31	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	44678	Medical
32	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	44678	Medical
33	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	44678	Medical
34	2022	aabzcdevx	Employee	222BSS65300	100H201250	PPO	Dependent	11/01/2022	44915	Medical
35	2022	aabzcdevx	Employee	222BSS65300	100H201250	PPO	Dependent	11/01/2022	44915	Medical
36	2023	aabzcdevx	Employee	22388578300	100H201250	PPO	Dependent	1/27/23	44986	Medical
37	2023	aabzcdevx	Employee	223X6485400	100H201250	PPO	Dependent	05/02/2023	45070	Medical
38	2023	aabzcdevx	Employee	223X6485400	100H201250	PPO	Dependent	05/02/2023	45070	Medical
39	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
40	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
41	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
42	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
43	2020	aadxbwzvx	Employee	22088710200	100H20HD	HDHP	Employee	1/28/20	43908	Medical
44	2020	aavzxxvcx	CCM	219MF340100	100H20CCM	CCM	Employee	10/31/19	43859	Medical
45	2020	aavzxxvcx	CCM	219MK056800	100H20CCM	CCM	Spouse	12/10/2019	43873	Medical
46	2020	aavzxxvcx	CCM	219MK061700	100H20CCM	CCM	Spouse	12/17/19	43873	Medical
47	2020	aavzxxvcx	CCM	219MK664000	100H20CCM	CCM	Employee	12/19/19	43880	Medical
48	2020	aavzxxvcx	CCM	22040453700	100H20CCM	CCM	Spouse	01/10/2020	43873	Medical
49	2020	aavzxxvcx	CCM	220D7276400	100H20CCM	CCM	Spouse	3/19/20	43936	Medical
50	2020	aavzxxvcx	CCM	220DD993100	100H20CCM	CCM	Spouse	8/24/20	44104	Medical
51	2020	aavzxxvcx	CCM	220GR322300	100H20CCM	CCM	Spouse	10/05/2020	44132	Medical
52	2020	aavzxxvcx	CCM	220KC170400	100H20CCM	CCM	Spouse	11/02/2020	44167	Medical
53	2020	aavzxxvcx	CCM	220KC170400	100H20CCM	CCM	Spouse	11/02/2020	44167	Medical
54	2022	aavzxxvcx	CCM	222B7031500	100H20CCM	CCM	Spouse	2/17/22	44629	Medical
55	2022	aavzxxvcx	CCM	222BD369500	100H20CCM	CCM	Spouse	3/23/22	44734	Medical
56	2022	aavzxxvcx	CCM	222BD369500	100H20CCM	CCM	Spouse	3/23/22	44734	Medical
57	2022	aavzxxvcx	CCM	221MX083700	100H20CCM	CCM	Spouse	10/29/21	44622	Medical
58	2022	aavzxxvcx	CCM	22246583900	100H20CCM	CCM	Spouse	1/19/22	44629	Medical
59	2022	aavzxxvcx	CCM	22252427300	100H20CCM	CCM	Employee	1/24/22	44608	Medical
60	2022	aavzxxvcx	CCM	222J4889000	100H20CCM	CCM	Employee	02/09/2022	44657	Medical
61	2022	aavzxxvcx	CCM	222BLP78200	100H20CCM	CCM	Spouse	11/07/2022	44909	Medical
62	2023	aavzxxvcx	CCM	223BT845700	100H20CCM	CCM	Spouse	5/22/23	45084	Medical
63	2023	aavzxxvcx	CCM	223GV946200	100H20CCM	CCM	Spouse	6/26/23	45126	Medical
64	2021	aawydzcyx	Employee	221W5640500	100H201250	PPO	Employee	06/09/2021	44405	Medical
65	2022	aawydzcyx	Employee	222L8458100	100H201250	PPO	Employee	03/09/2022	44671	Medical
66	2021	aaxdbydbd	Employee	221M4705300	100H20HD	HDHP	Employee	4/29/21	44342	Medical
67	2022	aaxdbydbd	Employee	222L8473200	100H20HD	HDHP	Employee	3/21/22	44671	Medical
68	2020	aaxxdxczx	Employee	220FH704800	100H201250	PPO	Employee	9/15/20	44132	Medical
69	2021	aaxxdxczx	Employee	221PN490100	100H201250	PPO	Employee	11/23/21	44538	Medical
70	2022	aaxxdxczx	Employee	22296695700	100H201250	PPO	Employee	2/15/22	44629	Medical
71	2022	aaxxdxczx	Employee	22296695700	100H201250	PPO	Employee	2/15/22	44629	Medical
72	2022	aaxxdxczx	Employee	222LH006400	100H201250	PPO	Employee	8/15/22	44804	Medical
73	2022	aaxxdxczx	Employee	222V4066100	100H201250	PPO	Employee	05/05/2022	44706	Medical
74	2022	aaxxdxczx	Employee	222Z2119000	100H201250	PPO	Employee	5/16/22	44720	Medical
75	2020	aazczdxad	Employee	220KT781700	100H201250	PPO	Spouse	11/09/2020	44181	Medical
76	2020	aazczdxad	Employee	220V5448800	100H201250	PPO	Employee	07/08/2020	44048	Medical
77	2020	aazczdxad	Employee	220X2205600	100H201250	PPO	Spouse	7/22/20	44125	Medical
78	2020	aazczdxad	Employee	220D6282800	100H20HD	HDHP	Employee	3/17/20	43936	Medical
79	2021	aazczdxad	Employee	22116547300	100H201250	PPO	Spouse	01/11/2021	44230	Medical
80	2021	aazczdxad	Employee	221CC450400	100H201250	PPO	Employee	7/19/21	44419	Medical
81	2021	aazczdxad	Employee	221CX613400	100H201250	PPO	Employee	08/02/2021	44433	Medical
82	2021	aazczdxad	Employee	221DT743800	100H201250	PPO	Spouse	08/10/2021	44440	Medical
83	2021	aazczdxad	Employee	221F5551800	100H201250	PPO	Spouse	3/18/21	44307	Medical
84	2022	aazczdxad	Employee	222FT169700	100H201250	PPO	Employee	07/12/2022	44769	Medical
85	2022	aazczdxad	Employee	222HH944100	100H201250	PPO	Employee	7/18/22	44776	Medical
86	2022	aazczdxad	Employee	222WD169600	100H201250	PPO	Employee	10/26/22	44881	Medical
87	2022	aazczdxad	Employee	222WD169600	100H201250	PPO	Employee	10/26/22	44881	Medical
88	2023	aazczdxad	Employee	223C8624600	100H201250	PPO	Employee	02/06/2023	45000	Medical
Data

Cell Formulas
Range	Formula
L2:L5	L2	=SORT(UNIQUE(A2:A88))
M2:M5	M2	=ROWS(UNIQUE(FILTER($B$2:$B$88,$A$2:$A$88=L2)))
Dynamic array formulas.

 

[realtoast]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year	Subscriber ID	CCM vs Employee	ClaimNumber	Plan	Group	Relationship	Date of Service	Paid Date	Product Name
2	2020	aaabawavd	Employee	220K9385600	100H201250	PPO	Employee	4/30/20	43978	Medical		2020	7
3	2020	aaabawavd	Employee	220K9385601	100H201250	PPO	Employee	4/30/20	44013	Medical		2021	6
4	2020	aaabawavd	Employee	220K9385602	100H201250	PPO	Employee	4/30/20	44013	Medical		2022	7
5	2021	aaabawavd	Employee	221LQ778700	100H201250	PPO	Employee	10/07/2021	44510	Medical		2023	4
6	2021	aaabawavd	Employee	221V4183600	100H201250	PPO	Employee	06/08/2021	44384	Medical
7	2022	aaabawavd	Employee	222Q1977200	100H201250	PPO	Employee	04/07/2022	44685	Medical
8	2022	aaabawavd	Employee	222SH650000	100H201250	PPO	Employee	9/22/22	44853	Medical
9	2023	aaabawavd	Employee	22366389300	100H201250	PPO	Employee	1/26/23	44972	Medical
10	2020	aaabawved	Employee	220LR303500	100H20HD	HDHP	Employee	11/19/20	44187	Medical
11	2020	aaabawved	Employee	220P7422100	100H20HD	HDHP	Employee	06/08/2020	44013	Medical
12	2020	aaabawved	Employee	220S5842400	100H20HD	HDHP	Employee	6/26/20	44034	Medical
13	2021	aaabawved	Employee	220MW565500	100H20HD	HDHP	Employee	12/28/20	44209	Medical
14	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
15	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
16	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
17	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
18	2020	aabzcdevx	Employee	220FX704900	100H201250	PPO	Dependent	9/29/20	44125	Medical
19	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
20	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
21	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
22	2020	aabzcdevx	Employee	220GN576800	100H201250	PPO	Dependent	9/29/20	44132	Medical
23	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/09/2020	44132	Medical
24	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/09/2020	44132	Medical
25	2020	aabzcdevx	Employee	220GR075300	100H201250	PPO	Dependent	10/09/2020	44132	Medical
26	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
27	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
28	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
29	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
30	2020	aabzcdevx	Employee	220GT598200	100H201250	PPO	Dependent	10/09/2020	44139	Medical
31	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	44678	Medical
32	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	44678	Medical
33	2022	aabzcdevx	Employee	222N8195100	100H201250	PPO	Dependent	3/29/22	44678	Medical
34	2022	aabzcdevx	Employee	222BSS65300	100H201250	PPO	Dependent	11/01/2022	44915	Medical
35	2022	aabzcdevx	Employee	222BSS65300	100H201250	PPO	Dependent	11/01/2022	44915	Medical
36	2023	aabzcdevx	Employee	22388578300	100H201250	PPO	Dependent	1/27/23	44986	Medical
37	2023	aabzcdevx	Employee	223X6485400	100H201250	PPO	Dependent	05/02/2023	45070	Medical
38	2023	aabzcdevx	Employee	223X6485400	100H201250	PPO	Dependent	05/02/2023	45070	Medical
39	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
40	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
41	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
42	2020	aadxbwzvx	Employee	219MK767400	100H20HD	HDHP	Spouse	12/16/19	43880	Medical
43	2020	aadxbwzvx	Employee	22088710200	100H20HD	HDHP	Employee	1/28/20	43908	Medical
44	2020	aavzxxvcx	CCM	219MF340100	100H20CCM	CCM	Employee	10/31/19	43859	Medical
45	2020	aavzxxvcx	CCM	219MK056800	100H20CCM	CCM	Spouse	12/10/2019	43873	Medical
46	2020	aavzxxvcx	CCM	219MK061700	100H20CCM	CCM	Spouse	12/17/19	43873	Medical
47	2020	aavzxxvcx	CCM	219MK664000	100H20CCM	CCM	Employee	12/19/19	43880	Medical
48	2020	aavzxxvcx	CCM	22040453700	100H20CCM	CCM	Spouse	01/10/2020	43873	Medical
49	2020	aavzxxvcx	CCM	220D7276400	100H20CCM	CCM	Spouse	3/19/20	43936	Medical
50	2020	aavzxxvcx	CCM	220DD993100	100H20CCM	CCM	Spouse	8/24/20	44104	Medical
51	2020	aavzxxvcx	CCM	220GR322300	100H20CCM	CCM	Spouse	10/05/2020	44132	Medical
52	2020	aavzxxvcx	CCM	220KC170400	100H20CCM	CCM	Spouse	11/02/2020	44167	Medical
53	2020	aavzxxvcx	CCM	220KC170400	100H20CCM	CCM	Spouse	11/02/2020	44167	Medical
54	2022	aavzxxvcx	CCM	222B7031500	100H20CCM	CCM	Spouse	2/17/22	44629	Medical
55	2022	aavzxxvcx	CCM	222BD369500	100H20CCM	CCM	Spouse	3/23/22	44734	Medical
56	2022	aavzxxvcx	CCM	222BD369500	100H20CCM	CCM	Spouse	3/23/22	44734	Medical
57	2022	aavzxxvcx	CCM	221MX083700	100H20CCM	CCM	Spouse	10/29/21	44622	Medical
58	2022	aavzxxvcx	CCM	22246583900	100H20CCM	CCM	Spouse	1/19/22	44629	Medical
59	2022	aavzxxvcx	CCM	22252427300	100H20CCM	CCM	Employee	1/24/22	44608	Medical
60	2022	aavzxxvcx	CCM	222J4889000	100H20CCM	CCM	Employee	02/09/2022	44657	Medical
61	2022	aavzxxvcx	CCM	222BLP78200	100H20CCM	CCM	Spouse	11/07/2022	44909	Medical
62	2023	aavzxxvcx	CCM	223BT845700	100H20CCM	CCM	Spouse	5/22/23	45084	Medical
63	2023	aavzxxvcx	CCM	223GV946200	100H20CCM	CCM	Spouse	6/26/23	45126	Medical
64	2021	aawydzcyx	Employee	221W5640500	100H201250	PPO	Employee	06/09/2021	44405	Medical
65	2022	aawydzcyx	Employee	222L8458100	100H201250	PPO	Employee	03/09/2022	44671	Medical
66	2021	aaxdbydbd	Employee	221M4705300	100H20HD	HDHP	Employee	4/29/21	44342	Medical
67	2022	aaxdbydbd	Employee	222L8473200	100H20HD	HDHP	Employee	3/21/22	44671	Medical
68	2020	aaxxdxczx	Employee	220FH704800	100H201250	PPO	Employee	9/15/20	44132	Medical
69	2021	aaxxdxczx	Employee	221PN490100	100H201250	PPO	Employee	11/23/21	44538	Medical
70	2022	aaxxdxczx	Employee	22296695700	100H201250	PPO	Employee	2/15/22	44629	Medical
71	2022	aaxxdxczx	Employee	22296695700	100H201250	PPO	Employee	2/15/22	44629	Medical
72	2022	aaxxdxczx	Employee	222LH006400	100H201250	PPO	Employee	8/15/22	44804	Medical
73	2022	aaxxdxczx	Employee	222V4066100	100H201250	PPO	Employee	05/05/2022	44706	Medical
74	2022	aaxxdxczx	Employee	222Z2119000	100H201250	PPO	Employee	5/16/22	44720	Medical
75	2020	aazczdxad	Employee	220KT781700	100H201250	PPO	Spouse	11/09/2020	44181	Medical
76	2020	aazczdxad	Employee	220V5448800	100H201250	PPO	Employee	07/08/2020	44048	Medical
77	2020	aazczdxad	Employee	220X2205600	100H201250	PPO	Spouse	7/22/20	44125	Medical
78	2020	aazczdxad	Employee	220D6282800	100H20HD	HDHP	Employee	3/17/20	43936	Medical
79	2021	aazczdxad	Employee	22116547300	100H201250	PPO	Spouse	01/11/2021	44230	Medical
80	2021	aazczdxad	Employee	221CC450400	100H201250	PPO	Employee	7/19/21	44419	Medical
81	2021	aazczdxad	Employee	221CX613400	100H201250	PPO	Employee	08/02/2021	44433	Medical
82	2021	aazczdxad	Employee	221DT743800	100H201250	PPO	Spouse	08/10/2021	44440	Medical
83	2021	aazczdxad	Employee	221F5551800	100H201250	PPO	Spouse	3/18/21	44307	Medical
84	2022	aazczdxad	Employee	222FT169700	100H201250	PPO	Employee	07/12/2022	44769	Medical
85	2022	aazczdxad	Employee	222HH944100	100H201250	PPO	Employee	7/18/22	44776	Medical
86	2022	aazczdxad	Employee	222WD169600	100H201250	PPO	Employee	10/26/22	44881	Medical
87	2022	aazczdxad	Employee	222WD169600	100H201250	PPO	Employee	10/26/22	44881	Medical
88	2023	aazczdxad	Employee	223C8624600	100H201250	PPO	Employee	02/06/2023	45000	Medical
Data

Cell Formulas
Range	Formula
L2:L5	L2	=SORT(UNIQUE(A2:A88))
M2:M5	M2	=ROWS(UNIQUE(FILTER($B$2:$B$88,$A$2:$A$88=L2)))
Dynamic array formulas.

Thank you! This works for my needs. Appreciated! (and, account updated).

 

[Fluff]

Glad we could help & thanks for the feedback.