Author: Oscar Cronquist Article last updated on November 19, 2018

Formula in C12:

=SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

How to create an array formula

  1. Double click on cell C12
  2. Paste above formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

Explaining formula in cell C12

Step 1 - Count values in cell range B3:B8

The COUNTIF function counts values equal to a condition or criteria.

COUNTIF($B$3:$B$8, $B$3:$B$8)

becomes

COUNTIF({"AA";"BB";"AA";"CC";"BB";"AA"}, {"AA";"BB";"AA";"CC";"BB";"AA"})

and returns

{3;2;3;1;2;3}

Step 2 - Divide 1 with array

1/COUNTIF($B$3:$B$8, $B$3:$B$8)

becomes

1/{3;2;3;1;2;3}

and returns

{0.333333333333333;0.5;0.333333333333333;1;0.5;0.333333333333333}

Step 3 - Sum values

The SUM function simply adds the numbers and returns the total.

SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))

becomes

SUM({0.333333333333333;0.5;0.333333333333333;1;0.5;0.333333333333333})

and returns 3.

Step 4 - Which values exist in cell range $D$3:$D$8

COUNTIF($B$3:$B$8, $D$3:$D$8)=0

becomes

COUNTIF({"AA";"BB";"AA";"CC";"BB";"AA"}, {"BB";"CC";"DD";"CC";"BB";"BB"})=0

becomes

{2;1;0;1;2;2}=0

and returns

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}

Step 5 - Convert TRUE to corresponding number

The IF function uses a logical expression (argument1) to determine which value to return (TRUE - argument2 , FALSE - argument3)

IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {0.333333333333333;0.5;1;0.5;0.333333333333333;0.333333333333333}, 0)

and returns

{0;0;1;0;0;0}.

Step 6 - Sum array

SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

becomes

SUM({0;0;1;0;0;0})

and returns 1.

Step 7 - Add numbers

SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

becomes

3+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

becomes

3+1

and returns 4 in cell D12.

Download Excel *.xlsx file

Count unique distinct values in two columns.xlsx

Related articles

Count unique distinct months

The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), [โ€ฆ]