Results 1 to 5 of 5
  1. #1
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,729
    Thanks
    148
    Thanked 156 Times in 149 Posts

    Excel count question

    I have a list of songs, many of which have more than one entry. I want to count the number of times each song is in the spreadsheet. so e.g.
    songA
    songA
    songA
    songB
    songB
    songB
    songB
    songC
    songD
    songD
    songD
    songD
    is the list of songs. Is there a function to say that songA is there 3 times, songB is 4, songC once etc.

    The list is much longer than this. It's a doddle in Access, but I can't use Access to do this (people I'm writing this for don't know it). I've not written a Excel VBA script before and I have managed to get the data in using VBA, but this has me stumped. It's probably easy though!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The easiest way I believe is to use a pivot table
    Insert - Pivot table <ok>
    Then drag the header with for the song titles to the row label and then drag it also to the Values section
    Steve
    Capture.PNG

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,729
    Thanks
    148
    Thanked 156 Times in 149 Posts
    Thanks Steve, but I have 2003 and pivot tables appear to be different to your graphic. However, looking at the data menu item, I noticed a subtotals option and that has a count facility, so I shall use that. I may be able to use Office 365 (the people this is for are getting it later this year) so I will see if pivot tables are the better option then.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try this: http://www.techonthenet.com/excel/pivottbls/create.php

    The title goes in the row field, and also into the data (to count it).

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    access-mdb (2014-05-30)

  6. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,729
    Thanks
    148
    Thanked 156 Times in 149 Posts
    Thanks Steve, just what the doctor ordered. I will keep your original guide and the page you linked to for further reference.

Posting Permissions

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