Formula for number of visits for each account number

[miguel08]

I am using excel office 365 and am having a issue. Creating the formula for a spreadsheet. Basically I have 2 tabs one has all the detailed data and on the other tab for each account number I need to be able to pull the total number of visits for each specific month in each column how do write the formula to get what I need on the spreadsheet? Here's the summary tab and the detailed tab :

TEST FILE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Account Number	NAME	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	Total
2	32144 Wc 10/16/19	Nikocevic, Bisera													0
3	33280	Madrid, Celia													0
4	33557 Nf 3/2/21	Dixon, Alice													0
5	30912	Gonzalez, Maria, E													0
6	32571	Mickens, Sharnice, L													0
7	33719	Valdovinos Gamez, Maria													0
8	21747	Winston, Marc, A													0
9	19247	Rivera Sr, Milton													0
10	17065	McGinness, Paul													0
11	14900	Hawk, Cleo													0
12	11202	Boyd, Queenie, E													0
13	33793	Draper, Anthony													0
14	13289	Dubois, Josephine, M													0
15	16666	MANNING, JOHN, J													0
16	33542NF 2/26/21	Callands, Stephanie													0
17	33630	Carrier, Jeffrey													0
18	15135	Hollingsworth, Rosalie, R													0
19	33550	Montiel, Alicia													0
20	33815	Patterson, Hyacinth													0
21	21539	Weddle, Giovanna													0
22	17656	Muccigrosso, Dorothea, T													0
23	33721	Martin, Kathrine, P													0
24	27113	Gonzalez, Norma													0
25	33289	Green, Wendy, S													0
26	33929	Santana, Maria													0
27	32751	Hood, Ron													0
28	33722	Jorban, Charnika													0
29	9775	Rizzo, Salvatore													0
30	32932 Nf 5/15/2020	Edwards, Angel													0
31	21140	Vadakel, Aleyamma, M													0
32	33726	Oriol, Chrissy													0
33	33736	Hernandez, Gabrielle													0
34	33656	Gonzalez, Leonel													0
35	33857	Silverman, Louise													0
36	33853	Schulman, Daniel													0
37	33854	Schulman, Mark Andrew													0
38	33636	Trujillo, Sara													0
39	33401	Tulimero, Rosanne, P													0
40	20981	Torres, Jose, A													0
41	30919wc 08/16/2018	Watts, Kawanna													0
42	33655	Long, David													0
43	21747	Winston, Marc, A													0
44	21539	Weddle, Giovanna													0
45	30919wc 08/16/2018	Watts, Kawanna													0
46	26066	VALENCIA, ANITA													0
47	21140	Vadakel, Aleyamma, M													0
48	33929	Santana, Maria													0
49	19623	Saini, Raj													0
50	9775	Rizzo, Salvatore													0
Summary

Cell Formulas
Range	Formula
O2:O50	O2	=SUM(C2:N2)

TEST FILE.xlsx
	A	B	C	D	E
1	Month	Account Number	SERVICE DATE	PVDR	PATIENT
2	JUL	32144 Wc 10/16/19	7/26/2021	PMD	Nikocevic, Bisera
3	JUL	33280	7/26/2021	PMD	Madrid, Celia
4	JUL	33557 Nf 3/2/21	7/26/2021	BA	Dixon, Alice
5	JUL	30912	7/26/2021	PMD	Gonzalez, Maria, E
6	JUL	32571	7/26/2021	BA	Mickens, Sharnice, L
7	JUL	33719	7/26/2021	PMD	Valdovinos Gamez, Maria
8	JUL	21747	7/26/2021	PMD	Winston, Marc, A
9	JUL	19247	7/26/2021	PMD	Rivera Sr, Milton
10	JUL	17065	7/26/2021	BA	McGinness, Paul
11	JUL	14900	7/26/2021	PMD	Hawk, Cleo
12	JUL	11202	7/26/2021	PMD	Boyd, Queenie, E
13	JUL	33793	7/26/2021	PMD	Draper, Anthony
14	JUL	13289	7/26/2021	BA	Dubois, Josephine, M
15	JUL	16666	7/26/2021	PMD	MANNING, JOHN, J
16	JUL	33542NF 2/26/21	7/26/2021	BA	Callands, Stephanie
17	JUL	33630	7/26/2021	PMD	Carrier, Jeffrey
18	JUL	15135	7/26/2021	PMD	Hollingsworth, Rosalie, R
19	JUL	15775	7/8/2021	PMD	Kershaw, Yolanda
20	JUL	31783	7/8/2021	PMD	Hodnett, Beverly, J
21	JUL	33865	7/8/2021	ELC	Griffiths, Mitzie
22	JUL	25138	7/8/2021	PMD	Faulkner, Mary
23	JUL	33835	7/8/2021	ELC	Fabiani, Rosann
24	JUL	33649	7/8/2021	PMD	Evans, Donna
25	JUL	33523	7/8/2021	PMD	Dunkley, Stephen, J
26	JUL	29258	7/8/2021	ELC	DeMasi, Regina, D
27	JUL	32658	7/8/2021	ELC	Davis, Electra, V
28	JUL	32747	7/8/2021	PMD	Correia, Silvie
29	JUL	32666 Wc 12-19-19	7/8/2021	PMD	Cisneros, Jose
30	JUL	10569	7/8/2021	PMD	Avery, Deborah
31	JUL	21900	7/7/2021	PMD	Zanko, Arthur, M
32	JUL	31849	7/7/2021	PMD	Zambrano, Hugo, E
33	JUL	21747	7/7/2021	PMD	Winston, Marc, A
34	JUL	30175	7/7/2021	BA	White, Mabeline, V
35	JUL	30919wc 08/16/2018	7/7/2021	PMD	Watts, Kawanna
36	JUL	33719	7/7/2021	PMD	Valdovinos Gamez, Maria
37	JUN	9775	6/30/2021	PMD	Rizzo, Salvatore
38	JUN	33826	6/30/2021	BA	Richy, James
39	JUN	17656	6/28/2021	PMD	Muccigrosso, Dorothea, T
40	JUN	32571	6/28/2021	BA	Mickens, Sharnice, L
41	JUN	16666	6/28/2021	PMD	MANNING, JOHN, J
42	JUN	33655	6/28/2021	PMD	Long, David
43	JUN	33727	6/28/2021	PMD	LEMPERT, CATHY
44	JUN	25235	6/28/2021	PMD	Lecount, Suzanne
45	JUN	9810	6/28/2021	PMD	Jones, Rhonda
46	JUN	32751	6/28/2021	PMD	Hood, Ron
47	JUN	25075	6/28/2021	PMD	Grillo, Ann Marie
48	JUN	33847NF05/25/21	6/28/2021	BA	Fresella, Malik
49	JUN	32932 Nf 5/15/2020	6/28/2021	PMD	Edwards, Angel
50	JUN	13289	6/28/2021	BA	Dubois, Josephine, M
51	JUN	33557 Nf 3/2/21	6/28/2021	BA	Dixon, Alice
52	JUN	33464	6/28/2021	BA	Davidson, Takiesha
53	JUN	33811 WC 5/20/21	6/28/2021	PMD	Cowan, Tanya
54	JUN	27828	6/28/2021	PMD	CONTRERAS, YANET, R
55	JUN	25632	6/28/2021	PMD	Chavez, Elva
56	JUN	33630	6/28/2021	PMD	Carrier, Jeffrey
57	JUN	33542NF 2/26/21	6/28/2021	BA	Callands, Stephanie
58	JUN	33791	6/28/2021	BA	Boggi, Anthony, J
59	JUN	32849	6/28/2021	PMD	Benjamin, Tariq, S
60	JUN	33765	6/28/2021	BA	Bailey, Zaneta
61	JUN	33092	6/28/2021	BA	Ayala, Christian
62	JUN	33563	6/28/2021	BA	Alcala Sr, Kendall
63	JUN	26932	6/28/2021	BA	Adjekughele, Jude, O
64	JUN	21900	6/25/2021	PMD	Zanko, Arthur, M
65	JUN	31849	6/25/2021	PMD	Zambrano, Hugo, E
66	JUN	21747	6/25/2021	PMD	Winston, Marc, A
67	JUN	30175	6/25/2021	BA	White, Mabeline, V
68	JUN	33683	6/25/2021	BA	Sofianoudis Drapeau, Tasoula
69	JUN	25363	6/25/2021	BA	Shain, LAWRENCE
70	JUN	18856	6/25/2021	BA	Quest, Lynette
71	JUN	30106	6/25/2021	PMD	Perez, Teresa
72	JUN	32144 Wc 10/16/19	6/25/2021	PMD	Nikocevic, Bisera
73	JUN	26148	6/25/2021	PMD	Mickens, Charlene, F
74	JUN	33727	6/25/2021	PMD	LEMPERT, CATHY
75	JUN	31783	6/25/2021	PMD	Hodnett, Beverly, J
76	JUN	33525	6/25/2021	BA	Herring, Adeline
77	JUN	33736	6/25/2021	PMD	Hernandez, Gabrielle
78	JUN	25075	6/25/2021	PMD	Grillo, Ann Marie
79	JUN	33847NF05/25/21	6/25/2021	BA	Fresella, Malik
80	JUN	30826NF 12/24/18	6/25/2021	PMD	Feliciano, Diane
81	JUN	33834 NF 5/21/21	6/25/2021	PMD	Elysee, Annie
82	MAY	32200	5/3/2021	PMD	Smith, Mark, J
83	MAY	33228 NF 08/27/19	5/3/2021	PMD	Siniscalchi, Angelica
84	MAY	33504	5/3/2021	PMD	Silva Honorio, Luiz
85	MAY	28032	5/3/2021	PMD	Neto, Artur, G
86	MAY	29768	5/3/2021	PMD	Miguel, Elida
87	MAY	33364	5/3/2021	PMD	Matias, Juan
88	MAY	33236	5/3/2021	BA	Martes, Natalie
89	MAY	33018	5/3/2021	BA	Mardenborough, Adina
90	MAY	33525	5/3/2021	BA	Herring, Adeline
91	MAY	33187	5/3/2021	PMD	Fanelli, Linda, K
92	MAY	29871	5/3/2021	BA	Dunham, John, L
93	MAY	13289	5/3/2021	BA	Dubois, Josephine, M
94	MAY	33557 Nf 3/2/21	5/3/2021	BA	Dixon, Alice
95	MAY	33438	5/3/2021	BA	Davilmar, Rickerby
96	MAY	12692	5/3/2021	BA	Daniel, Magdaline
97	MAY	33561	5/3/2021	PMD	Cozart, Dana
98	MAY	27828	5/3/2021	PMD	CONTRERAS, YANET, R
99	MAY	33468	5/3/2021	BA	Cancel, Lucy, J
100	MAY	33542NF 2/26/21	5/3/2021	BA	Callands, Stephanie
101	MAY	31618	5/3/2021	PMD	Calderon Chiara, Marlec
102	MAY	33414	5/3/2021	BA	Baxter-Cahill, Shelia, C
103	MAY	10723	5/3/2021	PMD	Barcia, Esther
104	MAY	33378	5/3/2021	PMD	Ashterman, Rowdon, L
105	APR	31849	4/30/2021	PMD	Zambrano, Hugo, E
106	APR	33336	4/30/2021	BA	Warrington, Kim
107	APR	20909	4/30/2021	PMD	Tiabo, Edmond
108	APR	33034	4/30/2021	BA	Sullivan, Desiree, T
109	APR	20299	4/30/2021	BA	Smith, Joan, M
110	APR	29107	4/30/2021	PMD	Sforza, John, A
111	APR	33511 Commercial	4/30/2021	BA	Scoca, Michael
112	APR	33463	4/30/2021	BA	Pessoa, Linda, E
113	APR	17552	4/30/2021	BA	Morgan Barrows, Clover, J
114	APR	33363	4/30/2021	BA	Martinez, Maria, E
115	APR	33236	4/30/2021	BA	Martes, Natalie
116	APR	33018	4/30/2021	BA	Mardenborough, Adina
117	APR	29768	4/7/2021	PMD	Miguel, Elida
118	APR	33363	4/7/2021	BA	Martinez, Maria, E
119	APR	33532	4/7/2021	PMD	Martinez, Eric
120	APR	33537	4/7/2021	PMD	Martinez Chavez, Jose, J
121	APR	33236	4/7/2021	BA	Martes, Natalie
122	APR	27017	4/7/2021	PMD	Liyanage, Neelamani, A
123	APR	15035	4/7/2021	PMD	HEWITT, VALERIE, A
124	APR	25075	4/7/2021	PMD	Grillo, Ann Marie
125	APR	32139 NF 10/26/19	4/7/2021	BA	Franco, Leticia
126	APR	30826NF 12/24/18	4/7/2021	PMD	Feliciano, Diane
127	APR	33441	4/7/2021	PMD	Douglass, Theresa
128	APR	33208NF10/08/2019	4/7/2021	PMD	Douglas, Carmen
129	APR	33438	4/7/2021	BA	Davilmar, Rickerby
130	APR	33561	4/7/2021	PMD	Cozart, Dana
131	APR	27828	4/7/2021	PMD	CONTRERAS, YANET, R
132	APR	12374	4/7/2021	PMD	Conte, Susan, A
133	APR	33468	4/7/2021	BA	Cancel, Lucy, J
134	APR	21674	4/6/2021	PMD	Williams, Evelyn
135	APR	21271	4/6/2021	PMD	Vaughnwellington, Kerry, A
136	APR	33228 NF 08/27/19	4/6/2021	PMD	Siniscalchi, Angelica
137	APR	33547	4/6/2021	PMD	Simmons, Nataya
138	APR	19392	4/6/2021	PMD	Rogers, Barbara, A
139	APR	33508	4/6/2021	PMD	Richards, Jovan
140	APR	33146	4/6/2021	PMD	Paolercio Jr, Joseph
141	MAR	31590 WC 6/8/19	3/3/2021	PMD	Osei, Margaret
142	MAR	25009	3/3/2021	PMD	Lorusso, Linda, A
143	MAR	15741	3/3/2021	PMD	Keltz, Theodore, N
144	MAR	33433	3/3/2021	PMD	Jimenez, Odilia Miranda
145	MAR	33223 Nf 11/19/2020	3/3/2021	PMD	Gerano, Jennie
146	MAR	32139 NF 10/26/19	3/3/2021	BA	Franco, Leticia
147	MAR	30826NF 12/24/18	3/3/2021	PMD	Feliciano, Diane
148	MAR	33208NF10/08/2019	3/3/2021	PMD	Douglas, Carmen
149	MAR	33348	3/3/2021	BA	Davis Elliott, Samantha
150	MAR	12374	3/3/2021	PMD	Conte, Susan, A
151	MAR	33352 WC 10/20/2020	3/3/2021	PMD	Bert, Annette
152	MAR	33428	3/3/2021	PMD	Amado Cardenas, Santiago
153	MAR	33413	3/2/2021	ELC	Thomas, Kaesha, N
154	MAR	33280	3/2/2021	PMD	Madrid, Celia
155	MAR	32751	3/2/2021	PMD	Hood, Ron
156	MAR	33062	3/2/2021	PMD	Deutsch, Ira
157	MAR	32658	3/2/2021	ELC	Davis, Electra, V
158	MAR	33361 Wc 1/15/21	3/1/2021	BA	Williams, Ila
159	MAR	27833	3/1/2021	PMD	TOURI, CONCETTA
160	MAR	33236	3/1/2021	BA	Martes, Natalie
161	MAR	29980	3/1/2021	PMD	Lima, Claudia
162	MAR	33345	3/1/2021	PMD	Grippi, Marta
163	MAR	33223 Nf 11/19/2020	3/1/2021	PMD	Gerano, Jennie
164	MAR	32139 NF 10/26/19	3/1/2021	BA	Franco, Leticia
165	MAR	30764	3/1/2021	PMD	Dibuono, Richard
166	MAR	32727 Nf 3/16/2020	3/1/2021	BA	Cea, Lorraine
167	MAR	32726 NF 03/16/2020	3/1/2021	PMD	Cea, Jillian
168	MAR	10645	3/1/2021	PMD	Bakalor, Donald
169	MAR	33428	3/1/2021	PMD	Amado Cardenas, Santiago
170	FEB	31590 WC 6/8/19	2/26/2021	PMD	Osei, Margaret
171	FEB	33353 Nf 01/16/21	2/26/2021	PMD	Nagpal, Ankita
172	FEB	31783	2/26/2021	PMD	Hodnett, Beverly, J
173	FEB	25075	2/26/2021	PMD	Grillo, Ann Marie
174	FEB	30826NF 12/24/18	2/26/2021	PMD	Feliciano, Diane
175	FEB	32932 Nf 5/15/2020	2/26/2021	PMD	Edwards, Angel
176	FEB	33428	2/26/2021	PMD	Amado Cardenas, Santiago
177	FEB	33413	2/25/2021	ELC	Thomas, Kaesha, N
178	FEB	15035	2/25/2021	PMD	HEWITT, VALERIE, A
179	JAN	32934 WC 9/7/2020	1/4/2021	BA	Jeanty, Marlene
180	JAN	29323	1/4/2021	BA	Hizam, Sommia, R
181	JAN	33005	1/4/2021	BA	Godoy, Candelaria
182	JAN	33223 Nf 11/19/2020	1/4/2021	PMD	Gerano, Jennie
183	JAN	33077	1/4/2021	PMD	Garcia, Kayla, L
184	JAN	12374	1/4/2021	PMD	Conte, Susan, A
185	JAN	33241	1/4/2021	BA	Clovis, Dionysia
186	JAN	12005	1/4/2021	PMD	Charles, Janet, O
187	JAN	31810	1/4/2021	PMD	Aviles-Gumbs, Yvette
188	JAN	33252	1/4/2021	BA	Amparo, Marcelina
DETAIL MASTER

 

[Alex Blakenburg]

Thank you for providing XL2BB sample data it makes it so much easier.
The formula itself if pretty straight forward and you can put this in C2 and copy it down and accross:

=COUNTIFS('DETAIL MASTER'!$B$2:$B$188,$A2,'DETAIL MASTER'!$A$2:$A$188,C$1)

Is there any reason you are not using a pivot table ?
What you are doing is a typical pivot table scenario.

Also as you have it, there are duplicates in the Summary sheet and also quite a number that are not in the summary sheet.

Even if you want to continue with your current format, you might want to at least change your DETAIL sheet to be an excel table.
You can then replace the $B$2:$B$188 to be table structured references so that it automatically expands as you add data to the table.

For example with the Detail table being called tblDetail,
the formula above would become

=COUNTIFS(tblDetail[Account Number],$A2,tblDetail[Month],C$1)

 

[miguel08]

Thank you for providing XL2BB sample data it makes it so much easier.
The formula itself if pretty straight forward and you can put this in C2 and copy it down and accross:

=COUNTIFS('DETAIL MASTER'!$B$2:$B$188,$A2,'DETAIL MASTER'!$A$2:$A$188,C$1)

Is there any reason you are not using a pivot table ?
What you are doing is a typical pivot table scenario.

Also as you have it, there are duplicates in the Summary sheet and also quite a number that are not in the summary sheet.

Even if you want to continue with your current format, you might want to at least change your DETAIL sheet to be an excel table.
You can then replace the $B$2:$B$188 to be table structured references so that it automatically expands as you add data to the table.

For example with the Detail table being called tblDetail,
the formula above would become

=COUNTIFS(tblDetail[Account Number],$A2,tblDetail[Month],C$1)

Thanks for the response. Normally, i use pivot tables a lot problem is that this spreadsheet has other columns in the spreadsheet that have to be filled in by staff and a pivot table wont work for what they need to accomplish.

Paula

 

[Alex Blakenburg]

Will the other formula I provided, solve your issue or do you still need help ?