How to count unique entries in a range

Counting unique entries within a range of cells is a common requirement when working with data, and it can be accomplished using a combination of functions. In this article, we’ll walk you through different methods to count unique values and provide step-by-step instructions.

What are the UNIQUE and COUNTA formulas?

The UNIQUE formula

The UNIQUE function is a tool that returns all the distinct or unique values from a specified range. This function eliminates any duplicate entries and provides only the unique values, whether from a single column, multiple columns, or rows.

Syntax:

=UNIQUE(range, [by_col], [exactly_once])

• range: The range of cells you want to extract unique values from.
• by_col (optional): A boolean value (TRUE or FALSE) that tells the function whether to compare values by columns or rows.
• exactly_once (optional): When set to TRUE, only the entries that appear exactly once will be returned.

The COUNTA formula

The COUNTA function counts the number of non-empty cells in a given range. When used together with the UNIQUE function, COUNTA helps you count how many unique entries exist in that range.

Syntax:

=COUNTA(range)

• range: The range of cells you want to count the non-empty values from.

Why use the UNIQUE and COUNTA formulas?

When combined with UNIQUE, COUNTA will count how many distinct values are returned by the UNIQUE function.

These formulas are extremely useful in data analysis, as they help streamline the process of identifying and counting distinct values in your dataset. Here are a few reasons to use UNIQUE and COUNTA:

1. Removing Duplicates: The UNIQUE function automatically filters out duplicates, making it easier to identify distinct values in a large dataset.
2. Data Analysis: When you need to analyze the diversity of data points (e.g., counting how many different products were sold, or how many unique users visited a website), UNIQUE and COUNTA help you quickly get these insights.
3. Data Cleaning: By counting unique values, you can assess the quality of your data and remove unnecessary duplicates, which is especially important in preparing data for reports or statistical analysis.

Using the UNIQUE and COUNTA functions to count unique entries

Now, let’s see in practice how to use this combination of formulas. To do so, we will use ONLYOFFICE Spreadsheet Editor.

Follow these steps:

1. Enter the data you need to analyze in the sheet.

2. Use =COUNTA(UNIQUE(range)) directly to count the unique entries in your range. There’s no need to split it into separate formulas.

For example, =COUNTA(UNIQUE(A2:A10)) will count all unique entries in the range A2:A10.

Example:

Suppose you have a list of colors in column B:

Now, apply the formula =COUNTA(UNIQUE(B2:B11)) in an empty cell to count the unique names.

Result:

The formula will return 7, since there are 7 unique colors in the list: white, red, yellow, pink, blue, green, brown.

Counting unique entries across multiple columns

When you need to count unique entries across multiple columns, ONLYOFFICE’s UNIQUE function operates row-wise by default, meaning it treats the entire row as a unique entity.

If you want to count unique values across different columns (e.g., columns A, B, and C), follow these steps:

1. Copy the values from columns B, C, and D into one single column (F).

2. Make sure to paste the values one after the other (Column B first, then Column C, then Column D).

3. Use =COUNTA(UNIQUE(F2:F31)) to count how many unique colors there are.

Counting unique entries with specific conditions (e.g., only values that appear once)

In addition to counting all unique entries, you may want to count only the entries that appear exactly once within your range. To do this in ONLYOFFICE, you can use the third argument of the UNIQUE function.

The syntax for the UNIQUE function is:

=UNIQUE(range, [by_col], [exactly_once])

Where the exactly_once argument, when set to TRUE, will return only the entries that appear exactly once in the dataset.

The formula to count values that appear exactly once is:

=COUNTA(UNIQUE(A2:A10, FALSE, TRUE))

Example:

We want to use the data we copied before in column F.

We are going to use the formula:

=COUNTA(UNIQUE(F2:F31, FALSE, TRUE))

The formula will return 9, since the following colors appear exactly once: white, gray, orange, mauve, purple, emerald, beige, cobalt and peach.

Conclusion

Counting unique entries is an essential and powerful tool for data analysis and reporting, and ONLYOFFICE Spreadsheet Editor offers an intuitive and flexible approach to achieve this. Just choose the technique that suits your case and start making your work smoother and faster.

Get ONLYOFFICE Spreadsheet Editor

Want to use this and other formulas? Get ONLYOFFICE Spreadsheet Editor and try these techniques now!

If you don’t have an ONLYOFFICE DocSpace account yet, you can create one for free right now and test the capabilities of its Spreadsheet Editor and all the other editors. Try it online or with our desktop applications.

START ONLINE     GET DESKTOP APP