# Phone number totaling

#### crgriggs

##### New Member
Hello, I have 313 phone number (some are the same) that I would like to turn in to a percentage, for instance, 70% of calls = (334) 555-0001, 15% = (334) 555-0008, and so on. I have tried to get this to work for 45 minutes but I am currently stumped. Thanks

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Can't you just do a countif of the phone number and divide it by the total number of calls?

I'm not real sure, my excel knowledge is very limited. Sorry but that is why I came here, hoping for an answer.

Here try this:
Select the entire column of your phone numbers, and run this code here.
let me know how it goes

Public Sub ReturnPercentage()

Dim rngToSearch As Range
Dim rngFiltered As Range
Dim rngCurrent As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)

rngToSearch.AdvancedFilter xlFilterCopy, , rngToSearch.Offset(0, 2), True

Set rngFiltered = Range(rngToSearch.Offset(0, 2).End(xlUp), rngToSearch.Offset(0, 2).End(xlDown))

For Each rngCurrent In rngFiltered

If rngCurrent.Offset(1, 0).Value <> "" Then
rngCurrent.Offset(1, 1).Formula = "=Countif(" & rngToSearch.Address & "," & _
rngCurrent.Offset(1, 0).Address & ")/" & "(" & rngToSearch.Rows.Count - 1 & ")"

rngCurrent.Offset(1, 1).NumberFormat = "0.0%"
End If

Next rngCurrent

End Sub

Just select any cell that has a phone number and run the code.

Public Sub ReturnPercentage()

Dim rngToSearch As Range
Dim rngFiltered As Range
Dim rngCurrent As Range

Set rngToSearch = Range(ActiveCell.EntireColumn.End(xlUp), ActiveCell.EntireColumn.End(xlDown))

Range(rngToSearch.Offset(0, 2), rngToSearch.Offset(0, 3)).Clear
rngToSearch.AdvancedFilter xlFilterCopy, , rngToSearch.Offset(0, 2), True

Set rngFiltered = Range(rngToSearch.Offset(0, 2).End(xlUp), rngToSearch.Offset(0, 2).End(xlDown))

For Each rngCurrent In rngFiltered

If rngCurrent.Offset(1, 0).Value <> "" Then
rngCurrent.Offset(1, 1).Formula = "=Countif(" & rngToSearch.Address & "," & _
rngCurrent.Offset(1, 0).Address & ")/" & "(" & rngToSearch.Rows.Count - 1 & ")"

rngCurrent.Offset(1, 1).NumberFormat = "0.0%"
End If

Next rngCurrent

End Sub

Thanks, this worked nicely.

Replies
5
Views
304
Replies
3
Views
296
Replies
4
Views
145
Replies
3
Views
188
Replies
1
Views
166

### Forum statistics

1,203,489
Messages
6,055,723
Members
444,814
Latest member
AutomateDifficulty

### 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.

### Which adblocker are you using?

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

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