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

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

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

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