# Count unique values in OpenOffice.org Calc

**Posted:**December 4, 2011

**Filed under:**OpenOffice.org, Solutions 7 Comments

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

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).

Right! Thanks a lot!

great!! thanks a lot

You are welcome!

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

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

Thank you.

Hi.

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

Thank you.