Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
MeatLightning
 
Posts: n/a
Default Count number of uniques starting with a given letter?

Hi all -
I have a spreadsheet for my iTunes library (cols: Song Name, Artist,
Album, etc). I want to count the number of unique artists that start with a
given letter (A for example).

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

As a result in order to count the number of artists in my collection, I
cannot simply count the number of entries in the Artist column (ColB in my
example above). I need to count the *unique* entries in the Artist column.
(I'm currently using this formula to do the artist count:
"SUMPRODUCT((B1:B100<"")/COUNTIF(B1:B100,B1:B100&""))".)

So how can I now count the number of unique artists in my collection that
start with a given letter?

any help would be greatly appreciated!!!
- meat.
  #2   Report Post  
Posted to microsoft.public.excel.misc
MeatLightning
 
Posts: n/a
Default Count number of uniques starting with a given letter?

sweet! that does it - thanks a bunch!

"Toppers" wrote:

Try:

=SUMPRODUCT((LEFT(B1:B100,1)="A" )/COUNTIF(B1:B100,B1:B100&""))

"MeatLightning" wrote:

Hi all -
I have a spreadsheet for my iTunes library (cols: Song Name, Artist,
Album, etc). I want to count the number of unique artists that start with a
given letter (A for example).

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

As a result in order to count the number of artists in my collection, I
cannot simply count the number of entries in the Artist column (ColB in my
example above). I need to count the *unique* entries in the Artist column.
(I'm currently using this formula to do the artist count:
"SUMPRODUCT((B1:B100<"")/COUNTIF(B1:B100,B1:B100&""))".)

So how can I now count the number of unique artists in my collection that
start with a given letter?

any help would be greatly appreciated!!!
- meat.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Uniques within a list based on value of cell... MeatLightning Excel Discussion (Misc queries) 3 March 20th 06 06:21 PM
Count the number of sheets. Olle Svensson Excel Discussion (Misc queries) 3 March 13th 06 03:30 PM
Count the number of unique records [email protected] Excel Worksheet Functions 7 March 8th 06 08:33 AM
Convert Text (letter) To Number Excel reloadinternet Excel Worksheet Functions 2 August 22nd 05 03:49 PM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM


All times are GMT +1. The time now is 10:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"