Can you help me devise a formula for counting unique values in a spreadsheet?

Lokesh N July 18, 2013
Pinterest Stumbleupon Whatsapp

In my first column I have a list of names and I need to get the sum of the total unique names as a numerical value.

e.g. the first column has:

lokesh
mahesh
venkat
lokesh
mahesh
venkat
lokesh
mahesh
venkat

I would need the formula to output “3”, because there are only 3 separate names in the column.

Ads by Google

  1. Paul P
    July 18, 2013 at 3:11 pm

    Make a copy of the column. Put it in another worksheet. Use remove duplicates on the Data menu and voila you get a count of unique names. For a formula though, that's more work and I'm not awake yet.

  2. Hovsep A
    July 18, 2013 at 8:07 am

    use a pivot table - first select your list (including the header) then go Data-Pivot Table and pivot Chart and follow the dialogue prompts to create a pivot table.

    Count of Each Item in a List See Also: Count of Items in List COUNTIF || Count Only One Occurrence
    http://www.ozgrid.com/VBA/count-of-list.htm