Using Pivot Charts to count names

Hello,
I am trying to create a pivot chart that can count how many times a persons name shows up in a table. The problem is, some of the same names are in different columns, and I just want a total count of each person. I created a sample chart that is somewhat similar to a table I am using. Any help would be great, and let me know if I did not explain myself good enough.
Thanks,
Branden

Originally Posted by NeedForExcel

Hi,

You cant do it with Pivot Tables..

Try this

Sub GetCountOfNamesOptimised()

    Dim Cell As Range, Counter As Integer, NewArr(), I As Integer
    
    With CreateObject("Scripting.Dictionary")
        For Each Cell In Range("A1").CurrentRegion.Offset(1, 1)
            If Not .Exists(Cell.Value) And Cell.Value <> "" Then .Add Cell.Value, Application.CountIfs(Range("A1").CurrentRegion, Cell.Value)
        Next Cell
        For Counter = 0 To .Count - 1
            If Not IsEmpty(.Keys()(Counter)) Then
                ReDim Preserve NewArr(1, I): NewArr(0, I) = .Keys()(Counter): NewArr(1, I) = .Item(.Keys()(Counter)): I = I + 1
            End If
        Next Counter
    End With
    
    Cells(Cells(Rows.Count, 1).End(xlUp).Row + 2, 1).Resize(UBound(NewArr, 2) + 1, 2).Value = Application.Transpose(NewArr)
    
End Sub

I am unable to attach a sample file for some reason. Let me know If you have any issues.

This works well but I am having trouble deciphering the code and using it for a more complex file. So I remade a sample file that matches it a little more closely. The names are coming from the "Data" sheet and I want them on the "Names" sheet. Thank you for taking the time to help me.

Based on your comment "I just want a total count of each person" why not just use a COUNTIF formula:-

=COUNTIF(Table1, "Tim")

And just create one for each name.

peterrc

Originally Posted by peterrc

Based on your comment "I just want a total count of each person" why not just use a COUNTIF formula:-

=COUNTIF(Table1, "Tim")

And just create one for each name.

peterrc

Well the problem with this is that the actual file I am using collects data from other worksheets. Sometimes I am collecting data from over 3000 files. So going through and seeing each different name might be a challenge sometimes.