Excel - Counting the values in a column

[wesleyw]

Hi guys, without knowing what string based values maybe present in a column on sheet1 how could I count up the top 5 strings in a column and display them on sheet2?

 

 

Thanks

 

 

 

Wes

[jinnantonnixx]

I'm not clear on what you mean by 'count up the top 5 strings', but perhaps this would help:

 

Use the sheet name in the reference.

 

Suppose sheet1 has the values you want to add in c5 to c10

 

On sheet2, you'd enter =sum(sheet1!c5:sheet1!c10) in the cell's value. If you're renamed your sheets, you'd use their names of course.

 

 

In LibreOffice, the separator is a dot (.) not a shriek (!)

Edited October 23, 2014 by jinnantonnixx

[LosOjos]

"Top 5" is going to need a Pivot Table - create one on your selected range, then use the column you want the Top 5 for as both the Row Label and Value. Make sure the Value display type is Count and ordered by Largest to Smallest (right click the value, Value Settings, Summarize By Count, Shw As Rank Largest to Smallest), finally filter your Row Label, choosing Value Filter > Top 10, which will let you alter it to Top 5 in a pop up.

 

Hope this helps, I'm rushing the explanation a bit!

[wesleyw]

Sorry. I suppose an example would be column A1 to 10 has values:

A1 cat

A2 dog

A3 gerbil

A4 hamster

A5 gerbil

A6 goldfish

A7 dog

A8 dog

A9 cat

A10 goldfish

 

 

Without knowing any of these before hand i would like something to count up the most popular pets?

[Geoff]

Pivot your data. Then use 'LARGE()' to find your top 5 values. Edited October 23, 2014 by Geoff

[wesleyw]

If i knew them beforehand it would be easy just =countif(column range,"cat") would be the way to go

[Seb1780]

I'm not sure what you mean by "string based values" but you could convert them to values using the =value() function and from there you can use =max() or =large() functions to get the top five. There is also a =maxa() function which works on strings but no corresponding "largea" function.

 

EDIT

 

Having now seen your example then a pivot table is the way to go, this will count the occurrences of each value and sort them by frequency.

Edited October 23, 2014 by Seb1780

[jinnantonnixx]

working with your range, Data > Filter > Number Filters - Top Ten Autofilter

change to 5 and you're rolling.

[Geoff]

There is also a =maxa() function which works on strings but no corresponding "largea" function.

 

Function LargeA(MyCells As Range, MyRank As Integer) As Integer

Dim MyData() As Variant

ReDim MyData(MyCells.Count)

'get data
DataCount = 1
For Each MyCell In MyCells

MyData(DataCount) = MyCell
DataCount = DataCount + 1

Next MyCell


For i = 1 To MyRank
For j = (i + 1) To (MyCells.Count)

If (MyData(j) > MyData(i)) Then

Temp = MyData(j)
MyData(j) = MyData(i)
MyData(i) = Temp
End If

Next j
Next i

LargeA = MyData(MyRank)

End Function