Count unique values in OpenOffice.org Calc

It may seem strange, but the OpenOffice spreadsheet Calc is missing a much needed ability to count how many unique values are there in the certain range of cells.

Luckily there are people who know how to fill in the gap, so here it is. A quick and simple method. Copy the formula below and paste it into your spreadsheet replacing the sample data range F2:F10 with the one you actually need.

=SUMPRODUCT((F2:F10<>"")/(COUNTIF(F2:F10;F2:F10)+(F2:F10="")))
Advertisements

7 Comments on “Count unique values in OpenOffice.org Calc”

  1. tohuwawohu says:

    Thanks for sharing this formula! There’s only a little typo: It has to be “…COUNTIF(F2:F10;F2:F10…” instead of “…COUNTIF(F2:F10,F2:F10…” (semicolon instead of comma between the two ranges).

  2. Dan says:

    It doesn’t work for me, the result is “#NAME?”.

  3. kk6ftd@gmail.com says:

    Boy, I’m really happy I found this solution. It saves a lot of trouble.

    Thank you.

  4. Gio says:

    Hi.

    I only get “TRUE” as result of this formula.

    Thank you.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s