+ Reply to Thread
Results 1 to 4 of 4

Count Uniques within a list based on value of cell...

  1. #1
    MeatLightning
    Guest

    Count Uniques within a list based on value of cell...

    Hi again -
    Not sure how to title this one but here goes. I have my iTunes library
    in excel (cols: Song Name, Artist, Album, etc). I want to count the number of
    albums by any given artist. Note that each record is a Song - meaning I have
    a row for each song on an album - the artist and album values stay the same
    while the Song Name changes. For ex:

    Col A ColB ColC
    Song 1 Artist Album
    Song 2 Artist Album
    Song 3 Artist Album

    I'd like to have a cell where I can type in a band name and this band name
    will be used in another cells' formula to calculate the number of albums I
    own by that artist. I know I can use COUNTIF to determine the number of songs
    by that artist. Unfortunately, I can't figure out how to count albums.

    Any ideas?

    Thanks in advance!
    meat


  2. #2
    Connie Martin
    Guest

    RE: Count Uniques within a list based on value of cell...

    Ever thought of doing a pivot table? It's amazing what data you can collect
    with pivot tables.

    "MeatLightning" wrote:

    > Hi again -
    > Not sure how to title this one but here goes. I have my iTunes library
    > in excel (cols: Song Name, Artist, Album, etc). I want to count the number of
    > albums by any given artist. Note that each record is a Song - meaning I have
    > a row for each song on an album - the artist and album values stay the same
    > while the Song Name changes. For ex:
    >
    > Col A ColB ColC
    > Song 1 Artist Album
    > Song 2 Artist Album
    > Song 3 Artist Album
    >
    > I'd like to have a cell where I can type in a band name and this band name
    > will be used in another cells' formula to calculate the number of albums I
    > own by that artist. I know I can use COUNTIF to determine the number of songs
    > by that artist. Unfortunately, I can't figure out how to count albums.
    >
    > Any ideas?
    >
    > Thanks in advance!
    > meat
    >


  3. #3
    Ron Coderre
    Guest

    RE: Count Uniques within a list based on value of cell...

    Try something like this:

    With a list of Song Name, Artist, Album in A1:C20

    G1: (enter an artist name here)
    H1: =SUMPRODUCT((B1:B20=G1)*(C1:C20<>"")/COUNTIF(C1:C20,C1:C20&""))

    That formula returns the album count for the artist in G2

    Does that help?

    ***********
    Regards,
    Ron

    XL2002, WinXP-Pro


    "MeatLightning" wrote:

    > Hi again -
    > Not sure how to title this one but here goes. I have my iTunes library
    > in excel (cols: Song Name, Artist, Album, etc). I want to count the number of
    > albums by any given artist. Note that each record is a Song - meaning I have
    > a row for each song on an album - the artist and album values stay the same
    > while the Song Name changes. For ex:
    >
    > Col A ColB ColC
    > Song 1 Artist Album
    > Song 2 Artist Album
    > Song 3 Artist Album
    >
    > I'd like to have a cell where I can type in a band name and this band name
    > will be used in another cells' formula to calculate the number of albums I
    > own by that artist. I know I can use COUNTIF to determine the number of songs
    > by that artist. Unfortunately, I can't figure out how to count albums.
    >
    > Any ideas?
    >
    > Thanks in advance!
    > meat
    >


  4. #4
    MeatLightning
    Guest

    RE: Count Uniques within a list based on value of cell...

    That's the winner!! Thanks a bunch. I need to learn me some about that
    SUMPRODUCT stuff.

    "Ron Coderre" wrote:

    > Try something like this:
    >
    > With a list of Song Name, Artist, Album in A1:C20
    >
    > G1: (enter an artist name here)
    > H1: =SUMPRODUCT((B1:B20=G1)*(C1:C20<>"")/COUNTIF(C1:C20,C1:C20&""))
    >
    > That formula returns the album count for the artist in G2
    >
    > Does that help?
    >
    > ***********
    > Regards,
    > Ron
    >
    > XL2002, WinXP-Pro
    >
    >
    > "MeatLightning" wrote:
    >
    > > Hi again -
    > > Not sure how to title this one but here goes. I have my iTunes library
    > > in excel (cols: Song Name, Artist, Album, etc). I want to count the number of
    > > albums by any given artist. Note that each record is a Song - meaning I have
    > > a row for each song on an album - the artist and album values stay the same
    > > while the Song Name changes. For ex:
    > >
    > > Col A ColB ColC
    > > Song 1 Artist Album
    > > Song 2 Artist Album
    > > Song 3 Artist Album
    > >
    > > I'd like to have a cell where I can type in a band name and this band name
    > > will be used in another cells' formula to calculate the number of albums I
    > > own by that artist. I know I can use COUNTIF to determine the number of songs
    > > by that artist. Unfortunately, I can't figure out how to count albums.
    > >
    > > Any ideas?
    > >
    > > Thanks in advance!
    > > meat
    > >


+ 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