count top 3 text values in column

[greyoxide]

I have a spreadsheet which contains survey results. I need to use excel to tally the results from each column and tell me the which text value occurs the most in each column.

Can this be done in excel?

 

[SkipVought]

hi,

You could use the COUNTIF() or COUNTIFS() function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

You could also use a PivotTable with the Top10 feature.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[greyoxide]

@Skip thanks for your quick reply. It looks like COUNTIF is what I should use, but there is a problem, it looks like the COUNTIF function uses a specific criteria to count, but I need it to count dynamically. Is this possible?

 

[SkipVought]

Yes, a function takes a particular argument. That is why I also suggested a PivotTable solution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MJPPaba]

you could use the RANK() function to establish the frequency of the values in the population.

 

[SkipVought]

I actually ran a test, where my PivotTable Top10(3) is just above the column of values. Whenever the frequency of values change the result is immediately reflected in the PivotTable above the column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

Skip, is this because you've put the pivot table on the same worksheet as the data? When I make a pivot-table using "insert - pivot table" in Excel2007, and accept the default of making the pivot table in a new worksheet, I can set its filter to show the top 3, and order by count, and it displays correctly, but if I add more data to the original worksheet, the pivot table doesn't reflect the changes until I do a "refresh" on it. I'd still, absolutely, go with the pivot-table approach on this, and thanks for the tip about Top10, I hadn't noticed this filter before.

 

[SkipVought]

The refresh can be made to be 'automatic' but using a few lines of VBA code, if you care to pursue in formu707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]