# Formulas to count UNIQUE values.

1. ## Formulas to count UNIQUE values.

I have a table with 3 columns, Song, Artist and PlayList and I need some additional counting formulas:

1. Formula to count the total number of unique entries in the Artist column.

2. Formula to count the number of unique entries in the Artist column where the first letter of each Artist entry is 'A', 'B', or 'C'.

3. Formula to count the number of unique entries in the Artist column where the first letter of each Artist entry is 'A', 'B', or 'C' AND
the Corresponding entry in the PlayList column is 'X'.

I have attached a sample workbook.

2. ## Re: Formulas to count UNIQUE values.

It may be not the neatest way but it does the job!!!

Please refer to the columns j,k,l and copy the formulas down.

It assumes the entries are sorted by artist

3. ## Re: Formulas to count UNIQUE values.

I appreciate the effort, but I will hold out for a "neater" way to do it. First, the data, if sorted at all, will be sorted by song name, and second, the actual spreadsheet I will be using this in is much wider, and with such "interim" result cells, there will be no guarantee the user will remember to deal with those as the list grows because they will be off the right side of the screen.

4. ## Re: Formulas to count UNIQUE values.

Try these...

This formula entered in E2:

=SUMPRODUCT(1/COUNTIF(B2:B17,B2:B17))

This array formula** entered in E7:

=SUM(IF(FREQUENCY(IF(LEFT(B2:B17)={"A","B","C"},MATCH(B2:B17,B2:B17,0)),ROW(B2:B17)-ROW(B2)+1),1))

This array formula** entered in E12:

=SUM(IF(FREQUENCY(IF(LEFT(B2:B17)={"A","B","C"},IF(C2:C17="X",MATCH(B2:B17,B2:B17,0))),ROW(B2:B17)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

5. ## Re: Formulas to count UNIQUE values.

Excellent! Thanks you. I never can seem to get my head around such formulas!

6. ## Re: Formulas to count UNIQUE values.

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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