Sometimes you want to count a long list of data in Excel, but there are many duplicates interfering with your statistics, and you can’t just delete them. At this point, is there a way to get rid of the influence of repeated data and count only the non-repeating data in a column?

As the answer to many other questions – the powerful Excel functions can help you. All you have to do is telling Excel the range of data you want to count, the criteria of counting, and of course, choosing a right formula.

How to Count the Non-repeating Data in a Column

1. If you want to count the data in column A, you can create a new column aside to assist.

2. Enter the formula in B2: =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)). “A2:A11” refers to the range of cells you want to count, you can change it according to your actual situation.

3. Press [Enter] key to work the formula out, the number of unduplicated data will be counted accurately and displayed in B2.