software

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 AColBColC
Song 1ArtistAlbum
Song 2ArtistAlbum
Song 3ArtistAlbum

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!
meatEver thought of doing a pivot table? It's amazing what data you can collect
with pivot tables.

quot;MeatLightningquot; wrote:

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

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:C20lt;gt;quot;quot;)/COUNTIF(C1:C20,C1:C20amp;quot;quot;))

That formula returns the album count for the artist in G2

Does that help?

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

XL2002, WinXP-Proquot;MeatLightningquot; wrote:

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

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

quot;Ron Coderrequot; wrote:

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