It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

Search
• FIRST POST
• hueknight1969
• 8th Feb 18, 9:52 PM
• 164Posts
• 22Thanks
hueknight1969
Hi

Is there a way to get excell to look at a group of cells and tell you the frequency of all the values without typing a specfic range or exactly the value. eg
1
1
2
2
2
4
6

1=2 times; 2=3times; 4=1 times; 6=1 times

I have 2800 entries to sort out, im sure this can be done (i have looked on google with no luck)

thanks

hugh
Page 1
• Cornucopia
• 8th Feb 18, 10:02 PM
• 10,015 Posts
• 9,893 Thanks
Cornucopia
Yes, there's probably several ways to do it.

I would do this:-

- Copy the list of values
- Sort the copied list in ascending order
- Add a de-duplication formula alongside the copied list (see below)
- Create a de-duplicated new list
- Add a counting formula to the de-duplicated list

Typical de-duplication formula for a vertical list:-

in column B, add: =IF(A1=A2,"",A1) to every cell next to a source value in column A

Typical counting formula for a vertical list:-

=COUNTIF("=" & C1, A: A)
Last edited by Cornucopia; 08-02-2018 at 10:21 PM.
I'm a Board Guide on the Phones & TV, Techie Stuff, In My Home,
The Money Savers Arms and Food Shopping boards. I'm a volunteer to help the boards run smoothly, and I can move and merge threads there. Any views (especially those on the UK TV Licence) are mine and not the official line of moneysavingexpert.com.

Board guides are not moderators. If you spot an inappropriate or illegal post then please report it to forumteam@moneysavingexpert.com
• WaywardDriver
• 8th Feb 18, 10:16 PM
• 108 Posts
• 109 Thanks
WaywardDriver
Insert a heading (A say) in the row above the numbers and Insert a PivotTable, drag the heading to both the Rows and Values sections, click where it says Sum of A, click Value Field Settings and change to Count.
• Tom99
• By Tom99 9th Feb 18, 12:29 AM
• 2,068 Posts
• 1,392 Thanks
Tom99
Yes a PivotTable will do it.
• stragglebod
• 9th Feb 18, 2:10 PM
• 114 Posts
• 92 Thanks
stragglebod
+1 pivot table
• rmg1
• By rmg1 9th Feb 18, 2:28 PM
• 2,912 Posts
• 742 Thanks
rmg1
Just to add in my 2p-worth.
A pivot table will certainly do what you want.
If you want to re-use it with ranges of varying sizes, research dynamic data ranges and then attach the pivot-table to one of those.
Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Live Stats

1,409Posts Today

7,862Users online