How to remove duplicates and count them.(Microsoft Excel)

Japanese version.

There are situations where you want to exclude duplicates of the same data and calculate the number of cases, but if you use the COUNT function, it will calculate the number of cases as duplicates.

Procedure

Sample data.
There are 15 cases, but 5 values are duplicated 3 times each.

Since this data is not numeric, the COUNTA function is used.

Exclude duplicates before counting.

If you do not need to keep the formulas on the sheet, you can remove duplicates and then use the
It is simple to use the COUNTA function.

Select the columns to be counted and choose Remove Duplicates from the Data menu.

Click the OK button when the Remove Duplicates window appears.

Tally up the range from which duplicates were removed and you are done.

Use UNIQUE function.

Use the result of the UNIQUE function as the argument to the COUNTA function.

=COUNTA(UNIQUE(cell_range))

SUMPRODUCT and COUNTIF functions.

If the UNIQUE function is not available, use the SUMPRODUCT and COUNTIF functions.

=SUMPRODUCT(1/COUNTIF(cell_range,cell_range))

---