Figure D Click OK. This feature will insert a subtotaling row below each group.

What constitutes a duplicate Within Excel, you can have duplicate values within the same column or you can have duplicate records -- a row where every value in the record is repeated.

Once you start counting duplicates, youll often discover that you need more.

For instance, you might need the opposite -- how many unique values are in the column.

There are several ways to count duplicate values and unique values.

You can work with most any dataset or download the.xlsx or.xls demonstration file (although specific instructions for the.xls format arent included in this article).

To read about finding (as opposed to counting) duplicates in Excel, check out my previous article, How to find duplicates in Excel.

LEARN MORE: Office 365 Consumer pricing and features Use COUNTIF() Figure A shows a COUNTIF() function that gets the job done.

Were not counting the number of actual duplicates but rather the number of times the value occurs within the given range.

If we were counting strictly duplicates, we wouldnt include the first occurrence of the value.

As you can see, the function returns the correct count, and its a quick fix.

Its great if you add and delete records, but itll also evaluate non-contiguous values.

If you enter one of the values below to the dataset, the function will add it to the count, as shown in Figure B, even if its not in the actual data range.

Removing the duplicates from view The COUNTIF() is adequate, but you might want a list of unique values rather than the full dataset.

In this case, you can use a PivotTable, as shown in Figure C.

To create this view, do the following: Click a Species value (any cell in B2:B5).

Click the Insert tab and then click PivotTable in the Tables group.

Accept all the default values in the resulting dialog -- simply click OK.

In the PivotTable Fields list (to the right), drag the Species field to the Row Labels and the Values sections.

Figure C Dragging the same to both sections forces a unique count.

Using Subtotal One of the first two solutions will probably satisfy most situations, but you could also use Excels Subtotal feature, which evaluates data by groups.

If thats not a problem, or its what youre doing anyway, you might want to consider using the Subtotal feature: First, sort the data by clicking inside the column you want to sort.

Then, click Sort Filter in the Editing group (on the Home tab), and choose Sort A To Z from the drop-down list.

In this case, select the Species field and specify the Count function ( Figure D ).

Figure D Click OK.

This feature will insert a subtotaling row below each group.

Excel For Mac Remove Duplicates