How to count non duplicate values in excel

Answer

To count non-duplicate values in Excel, you can use built-in formulas and functions depending on whether your data is numeric, text, or a mix. Non-duplicate values are the unique entries that appear only once in a range.

Method 1: Using COUNTIF with SUMPRODUCT

1. Select the range of data. Suppose your data is in column A from A2 to A20.

2. Use this formula to count values that appear only once:

   =SUMPRODUCT(--(COUNTIF(A2:A20,A2:A20)=1))

Explanation:

• COUNTIF(A2:A20,A2:A20) counts how many times each value appears in the range.

• =1 checks for values that appear only once.

• -- converts TRUE/FALSE results to 1/0.

• SUMPRODUCT sums all 1s, giving the total count of non-duplicate values.

Method 2: Using UNIQUE and COUNTA (Excel 365 or Excel 2021)

1. Suppose your data is in A2:A20.

2. Enter this formula:

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

Explanation:

• UNIQUE(A2:A20, TRUE, FALSE) returns only values that appear once.

• COUNTA counts the number of unique values returned.

Method 3: Using a Helper Column

1. In column B, next to your data, enter this formula in B2:

   =IF(COUNTIF(A$2:A$20,A2)=1,1,0)

2. Drag the formula down to match your data range.

3. Sum column B to get the count of non-duplicate values:

   =SUM(B2:B20)

Notes and Tips

• Text values and numbers are treated the same in COUNTIF.

• Blank cells are ignored if using COUNTIF but counted in some variations of UNIQUE.

• UNIQUE is available only in newer Excel versions. Older versions require COUNTIF or helper columns.

This method works efficiently for lists of hundreds or thousands of entries, making it easy to identify strictly non-duplicate values.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report