counting non numeric with pivot tables

[Jenst]

Hi! I have a table in which I have three non numeric columns and I want to count them(G1,simplyfied). I only get it managed to count one of the columns in a pivot table, but I want to have the result you can see in G3 in a pivot table.
Has anyone an idea how to do it, at first without vba?
G1



Rating1
Rating2
Rating3


a
a
b


a
b
b


a
a
b


c
c
a



G3


Category
Count



a

6



b
4



c
2






Many thanks, Jens

[mana]

Why do you want to use pivot?
Why don't you want to use VBA?

It's easy to achieve it by VBA.

[Jenst]

Hi Mana, ok, I have done it without pivot tables now. ;)

[mana]

Please try this.


Option Explicit

Sub test()
Dim dic As Object
Dim c As Range

Set dic = CreateObject("scripting.dictionary")

For Each c In Sheets("sheet1").Range("a2:c5")
dic(c.Value) = dic(c.Value) + 1
Next

With Sheets("sheet2")
.UsedRange.ClearContents
.Range("a1:b1").Value = Array("category", "count")
.Range("a2").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.keys)
.Range("b2").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.items)
End With


End Sub

[Jenst]

Great, even simpler than my solution. Thank you mana for all your tips and helping solutions.