+ Reply to Thread
Results 1 to 6 of 6

Show number of times a text appears in pivot table (graph)?

  1. #1
    Registered User
    Join Date
    02-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    26

    Show number of times a text appears in pivot table (graph)?

    Hi!

    I have a table like
    20181001-apple
    20181002-apple
    20181005-banana
    20181015-banana
    20181020-apple
    20181027-pineapple
    20181101-apple
    20181101-banana
    20181103-cucumber
    And so on

    I have made a pivot table where I can group October and November together.
    But I can't manage to display how many times they appear in each month. Like:

    October-apple 3,banana 2, pineapple 1
    November-apple 1, banana 1, cucumber 1

    And then make it into a graph. Do I need countif or something?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,393

    Re: Show number of times a text appears in pivot table (graph)?

    Something like this!
    Attached Files Attached Files
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    02-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    26

    Re: Show number of times a text appears in pivot table (graph)?

    Quote Originally Posted by jeffreybrown View Post
    Something like this!
    Thanks for the help, that is exactly what I want... But when I now create my pivot-table-graph I can't manage to sort my own table for each month. I can do it on your file and I can do it in my original table. But now when I create a pivot-table, when I can only check/uncheck each date (each date this year) and not sort under month.
    What is wrong?

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,393

    Re: Show number of times a text appears in pivot table (graph)?

    My first thought, your dates do not look like real dates.

    If your "dates" are really in this format, 20181001, then try this to convert them into real dates.

    =--TEXT(A1,"0000\/00\/00")

    Not apply a date format to them

  5. #5
    Registered User
    Join Date
    02-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    26

    Re: Show number of times a text appears in pivot table (graph)?

    jeffreybrown,

    Thanks, but the problem was that I tried to group like in a normal table. Worked fine now when I right-clicked and pressed "group" :D

    Thanks for all the help!
    Last edited by jeffreybrown; 11-12-2018 at 06:43 PM. Reason: Removed full quote!

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,393

    Re: Show number of times a text appears in pivot table (graph)?

    Good to hear. Glad you now have a working solution and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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