Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

help with an Excel formula


  • Please log in to reply

#1
diggeryo

diggeryo

    Member

  • Member
  • PipPipPip
  • 343 posts
I'm trying to figure if there's a formula I can use in Excel 2003 that will count the number of unique words that appear in a certain column.

For example, in the file in question, column A has a list of songs and column B has a list of artists. Obviously since artists write more than one song, an artist might appear more than once in column B. I'd like to add the number of different artists that appear.

So if this were my Excel file...

COL A - COL B
Brown Sugar - Rolling Stones
Start Me Up - Rolling Stones
Light My Fire - Doors
Iron Man - Black Sabbath
---------------------------------------------------
...the result for the formula would be 3, since there are 3 unique artists in column B.

Thank you for any advice.

mike.
  • 0

Advertisements


#2
paper

paper

    Member

  • Member
  • PipPip
  • 48 posts
Say, we need to count the unique words in B1 : B4, try the Array Formula
=SUM(1/COUNTIF(B1:B4,B1:B4))
(To enter an Array Formula we need to press Ctrl+Shift+Enter, rather than just press Enter)
  • 0

#3
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
I can't believe that worked. Thank you so much! And here I thought it was a futile quest to find a formula to do what I wanted it to do.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP