Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

sum up the number of times "like" data appears in cells


how do I calculate how many times "like" data appears in cells?

also, I do not want to have "like" data to appear in the column


column A:

bob 1
bob 2
tom 5
tom 3
jane 10
jane 12
jane 5

expected results:

bob 2 (number of times bob appears in column a)

tom 2 (number of time tom appears in column a)

jane 3 (number of times jane appears in column a)



Selected Answer

Hi and welcome to the Forum

There are several ways to  calculate "Number of times" (or "count" as Excel describes it).

In the attached spreadsheet, data like yours (with some repeats) is in cells A2 to A13.

In column C, I've given you an example of "Using a formula", specifically Excel's COUNTIF function. The formula in cell C3 is:

=A3&" ("&COUNTIF($A$3:$A$13,A3)&")"

to give the result "bob 1 (3)", as you asked. That's copied down but the limitation is you need to know how many different names there are (or suffer repeats).

In column E, I used a more sophisticated feature (a Pivot Table) whose powerful data analysis features mean you don't have to write a formula. I highlighted A2:A13 and used file menu Insert/ Tables/ Pivot Table (existing sheet). I then dragged the Name field into both the Rows and Values boxes in the PivotTable Field interface (which pops up). It gave a table where the default second column was the Count of the different names in the data (with no need to know how many- it works that out).

You can find out about such methods from some of Don's tutorials, e.g. Count the Number of Cells that Contain Specific Text in Excel and Easy Pivot Table in Excel (Auto-Subtotals Feature)..

Hope this helps. if so, please remember to mark this Answer as Selected.



Hope that helped. Thanks for selecting my answer. 
John_Ru (rep: 4312) Nov 11, '22 at 1:12 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login