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.)
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
sample:
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)
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.