When working with data, it is common to have duplicate values in a range. Please check here to count the number of unique values in a range. In this post, we will figure out how to count the number of cells without duplicates in a range, or the numbers only appear once.

The COUNTIF function counts the number of cells that meet a criterion, and the SUMPRODUCT function returns the product of numbers in an array.

**Example:** In the following database, the first name is in column A, the last name is in column B, and the salary is in column C.

**Formula 1: **To count the number of cells without duplicates in column B

=SUMPRODUCT(–(COUNTIF(B2:B12,B2:B12)=1))

=SUMPRODUCT((COUNTIF(B2:B12,B2:B12)=1)*1)

- Step 1: COUNTIF(B2:B12, B2:B12): to count the frequency of each value (e.g., the frequency of JONES is 2);

- Step 2: (COUNTIF(B2:B12, B2:B12)=1): to check the frequency of value, and returns true if it equals 1, or false if not equals 1;
- Step 3: The double hyphens (or times 1): to convert the logical value true into one and false into zero;

- Step 4: The SUMPRODUCT function returns the sum of the products of the new array.

In the example, there are three values in column B that have no duplicates.

**Formula 2: **To count the number of cells in column C that have no duplicates

=SUMPRODUCT(–(COUNTIF(C2:C12,C2:C12)=1))

=SUMPRODUCT((COUNTIF(C2:C12,C2:C12)=1)*1)

- Step 1: COUNTIF(C2:C12, C2:C12): count each value's frequency (e.g., 84,923 is 2);
- Step 2: (COUNTIF(C2:C12, C2:C12)=1): to check the frequency of value, and returns true if it equals 1, or false if not equals 1;
- Step 3: The double hyphens (or times 1): convert the logical value true into one and false into zero;
- Step 4: The SUMPRODUCT function returns the sum of the products of the new array.

In the example, four values in column C have no duplicates.

**Notes: The SUMPRODUCT Function**

The SUMPRODUCT function adds all the multiplication results for all arrays.

**Formula:**

=SUMPRODUCT(array1, [array2], …)

**Explanations:**

– Array1 is required; the first array is to multiply and then add.

– Array2 is optional; the second array is to multiply and then add.