Count number of Occurrences of Name in list

[mba_110]

Hi everyone,

I want to count number of occurrences of name (Text) in list, the list consist of 230 entries and their is unique names is 83 but most of them are repeated. if i am using countif function then i have to go each time to type the name in inverted commas "Name" which is not possible all the time.

I will give you a small example.

Column A from 1 to 230 has a names and i want formula at B1, the range will be A1 to A230 and the Criteria will be A1 and not "" commas.

The formula should check how many times the name on A1 is repeated until A230, it should give me total in numeric value in formula cell which is B1, remember i don't want to put " " for criteria i want criteria as Cell address A1.

I hope it is clear to understand.

 

[Cubist]

What are inverted commas? Do you mean double quotes? ""
Your example is the same as your description of the problem. It would be helpful if you provide a few rows of samples and the expected outcome.

 

[alansidman]

you could also use the GroupBy function if your are using the Beta version of 365. If not then you could use the GroupBy function in Power Query and count the number of hits.

 

[Ludwig]

If I read your question right, does

=COUNTIF( A1:A230, A1 )

work for you?

NB: I usually use the "S" versions of these sort of functions nowadays - you never know when you might want to add "if" criteria.

=COUNTIFS( A1:A230, A1 )

Not so essential with COUNTIF, but definitely with SUMIFS etc.

 

[Alex Blakenburg]

I think we are all a bit unclear on what you want and as Cubist suggested a view of some sample data and expected outcomes would be useful.

See if any of this points you in the right direction.

20240421 Countif mba_110.xlsx
	A	B	C	D	E	F	G
1	John	6	<--- Option 1		Option 2 --->	John	6
2	Peter	4				Peter	4
3	John	6				Mary	4
4	Mary	4				Sam	2
5	Sam	2				Doug	2
6	Doug	2				0	0
7	John	6
8	Mary	4
9	Peter	4
10	John	6
11	Peter	4
12	John	6
13	Mary	4
14	Sam	2
15	Doug	2
16	John	6
17	Mary	4
18	Peter	4
19
20
Option1

Cell Formulas
Range	Formula
F1:F6	F1	=UNIQUE(A1:A230)
G1:G6	G1	=COUNTIFS(A1:A230,F1#)
B1:B18	B1	=COUNTIFS($A$1:$A$230,A1)
Dynamic array formulas.

 

[alansidman]

One more way

Book3
	A	B	C	D
1	John		Doug	2
2	Peter		John	6
3	John		Mary	4
4	Mary		Peter	4
5	Sam		Sam	2
6	Doug		Total	18
7	John
8	Mary
9	Peter
10	John
11	Peter
12	John
13	Mary
14	Sam
15	Doug
16	John
17	Mary
Sheet1

Cell Formulas
Range	Formula
C1:D6	C1	=GROUPBY(A1:A18,A1:A18,COUNTA)
Dynamic array formulas.