+ Reply to Thread
Results 1 to 3 of 3

How to show the frequency amount

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    11

    Red face How to show the frequency amount

    Hello!

    I have a list of numbers (1-60) with some duplicates and I want to organize them so that is shows the frequency that the number appears next to the list of numbers without duplicates.
    So for example:

    3
    4
    5
    5
    4
    6

    I would want it to show like this:

    3 1
    4 2
    5 2
    6 1

    I was told to use pivot table but I'm not sure how to go about it.

    Many thanks,
    Cheers!
    Last edited by charmaine143; 07-19-2018 at 12:48 AM.

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,053

    Re: How to show the frequency amount

    Let's say that your list of duplicates is in range A2:A100 and your unique numbers are in C2:C10. in D2 and down: =COUNTIF($A$2:$A$100,$C2)
    Or set a pivot: Select the data - Insert - Pivot table - add the numbers to Rows and to values and set it as Count.
    Click the * to say thanks.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,623

    Re: How to show the frequency amount

    In Excel you have dedicated function for Frequency - not that strange it's called FREQUENCY.

    If you want to use full power of this function - it is a bit tricky, because it shall be committed as array formula*

    *...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    So I wrote 1...60 in column D, then selected cells E1:E60 and having them all selected (not just one) I wrote a formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and committed it with Ctrl+Shift+Enter
    Attached Files Attached Files
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Forecasting amount across a monthly column based on frequency of sales
    By eagleadam89 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2014, 10:23 AM
  2. sum and frequency of data & sum of amount based on criteria
    By joeldlow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-09-2014, 02:28 AM
  3. [SOLVED] Graphing Frequency, Zero Value doesn't show up....
    By Exxcel Noob in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-03-2012, 12:54 AM
  4. Show Data Lables show Amount & Persentage at the same time
    By ComcoDG in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-29-2008, 04:54 AM
  5. If Negative Show Amount, If Positive Show Zero
    By rdusseau in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2008, 11:25 AM
  6. Need To Complete Formular To Sort And Show Frequency
    By travelersway in forum Excel General
    Replies: 2
    Last Post: 07-28-2005, 02:53 PM
  7. Need A Formular To Sort And Show Frequency
    By travelersway in forum Excel General
    Replies: 6
    Last Post: 07-27-2005, 11:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1