Summary of the statistical formula for the number of unique values in excel

There are seven formulas for the number of unique values in excel. The array formula contains braces. Press CTRL + SHIFT + ENTER to end the input.

The code is as follows:	Copy code
1 {= SUM (1/COUNTIF (B1: B44, B1: B44 ))} 2 {= SUM (N (MATCH (B1: B44, B1: B44,) = ROW )))} 3 {= SUMPRODUCT (B1: B44 <> "")/COUNTIF (B1: B44, B1: B44 &""))} 4 = SUMPRODUCT (1/COUNTIF (B1: B44, B1: B44 )) 5 {= SUM (N (FREQUENCY (B2: B45, B2: B45)> 0 ))} 6 {= SUM (-- (MATCH (B1: B44, B1: B44,) = ROW )))} 7 {= SUM (-- (FREQUENCY (B1: B44, B1: B44)> 0 ))}

Instance

Table "1" is a summary table, and table "2" is a statistical table. How can I count the number of customers in the same region in table "2", but the number of customers in the same region is only counted once?

Method 1

The code is as follows:	Copy code
B2 = SUMPRODUCT ('1 '! B $2: B $11 = A2)/COUNTIF ('1 '! C $2: C $11, '1 '! C $2: C $11 ))

Method 2

The code is as follows:	Copy code
B2 = SUM ('1 '! $ B $2: $ B $11 = A2) * IF (MATCH ('1 '! $ C $2: $ C $11, '1 '! $ C $2: $ C $11,) = ROW ($ C $1: $ C $10), 1, 0 ))

Method 3
B2

The code is as follows:	Copy code
= SUMPRODUCT ('1 '! B $2: $ B $11 = A2) * (MATCH ('1 '! $ B $2: $ B $11 & '1 '! $ C $2: $ C $11, '1 '! $ B $2: $ B $11 & '1 '! $ C $2: $ C $11,) = ROW ('1 '! $ C $2: $ C $11)-1 ))