x
Solved

# Count like values in multiple columns of a table

Posted on 2011-03-16
Medium Priority
175 Views
I am attempting to count the number of times someones name displays in a cell.

Example

0
Question by:kel-o
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Expert Comment

ID: 35151797
So what results do you want?

If you want to count a specific name in that whole range try COUNTIF, e.g.

=COUNTIF(A\$2:E\$100,G2)

where G2 contains a specific name

regards, barry
0

Author Comment

ID: 35151972
Hi Barry,
Thank you for the response. I am trying to get a count of how many times a unique name shows up in the entire table. For example, I want to have a count of how many times Stacy Snyder's name shows up in the entire table. End result - I would like to have one column show all the unique names and a column next to it count how many times that name displayed in the table.
Regards,
Kelly
0

LVL 39

Accepted Solution

nutsch earned 1000 total points
ID: 35152682
Hi Kelly,

The attached code should do what you require.

Thomas

``````Sub Macro2()
Dim sht As Worksheet, rg As Range, i As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set rg = Range("A1").CurrentRegion.Offset(1)
rg.Copy sht.[a1]

With sht

For i = 1 To rg.Columns.Count

If i > 1 Then .Range(.Cells(1, i), .Cells(Rows.Count, i).End(xlUp)).Cut .Cells(.Rows.Count, 1).End(xlUp).Offset(1)

Next

.Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlNo

.Range("B1:B" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=countif('" & rg.Parent.Name & "'!" & rg.Address(True, True, xlR1C1) & ",RC[-1])"

End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month15 days, 8 hours left to enroll