Count number of Occurrences of Name in list

mba_110

Board Regular
Joined
Nov 28, 2012
Messages
72
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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.
 
Upvote 0
If I read your question right, does
Excel Formula:
=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.
Excel Formula:
=COUNTIFS( A1:A230, A1 )
Not so essential with COUNTIF, but definitely with SUMIFS etc.
 
Upvote 0
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
ABCDEFG
1John6<--- Option 1Option 2 ---> John6
2Peter4Peter4
3John6Mary4
4Mary4Sam2
5Sam2Doug2
6Doug200
7John6
8Mary4
9Peter4
10John6
11Peter4
12John6
13Mary4
14Sam2
15Doug2
16John6
17Mary4
18Peter4
19
20
Option1
Cell Formulas
RangeFormula
F1:F6F1=UNIQUE(A1:A230)
G1:G6G1=COUNTIFS(A1:A230,F1#)
B1:B18B1=COUNTIFS($A$1:$A$230,A1)
Dynamic array formulas.
 
Upvote 0
One more way
Book3
ABCD
1JohnDoug2
2PeterJohn6
3JohnMary4
4MaryPeter4
5SamSam2
6DougTotal18
7John
8Mary
9Peter
10John
11Peter
12John
13Mary
14Sam
15Doug
16John
17Mary
Sheet1
Cell Formulas
RangeFormula
C1:D6C1=GROUPBY(A1:A18,A1:A18,COUNTA)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top