Stephen asks:

Could you help me with (1) the Count the number of unique Divisions where ALL products have been delivered. (2) The Count the number of unique Divisions where NOT ALL products have been delivered. Determined by the presence or absence of a Delivery Date.

Three columns of data, listed below.

Division Product Delivered Date
AN9 CPPR.T014.AN9.QD.R00002.F090101.T130926.F001 28-Sep
GH3 CPPR.T014.GH3.QD.R00002.F090101.T130926.F001 29-Sep
L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F001 30-Sep
L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F002 29-Sep
L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F003
L53 CPPR.T014.L53.QD.R00002.F090101.T130926.F001
L7W CPPR.T014.L7W.QD.R00002.F090101.T130926.F001
NHP CPPR.T014.NHP.QD.R00002.F090101.T130926.F001
L3N CPPR.T014.L3N.QD.R00002.F090101.T130926.F001 24-Sep
WH3 CPPR.T014.WH3.QD.R00002.F090101.T130926.F001
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F001 25-Sep
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F002 25-Sep
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F003 26-Sep
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F004 27-Sep

Table of contents

  1. Count unique distinct values with a condition
  2. Filter unique distinct values with a condition
  3. Highlight unique distinct values with a condition

Count unique distinct values with a condition

Unique distinct values with a condition

Array formula in cell F18:

=SUM(IF($C$2:$C$15<>"", (1/COUNTIFS(A2:A15,A2:A15,C2:C15,"<>"&"")), 0))

Array formula in cell F19:

=SUM(IF($C$2:$C$15="", (1/COUNTIFS(A2:A15,A2:A15,C2:C15,"="&"")), 0))

Explaining array formula in cell F18

Step 1 - Check if there are values in cell range C2:C15

$C$2:$C$15<>""

becomes

{41545;41546;41547;41546;0;0;0;0;41541;0;41542;41542;41543;41544}<>""

and returns

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

Step 2 - Count values in column A and column C equal to nothing

COUNTIFS(A2:A15,A2:A15,C2:C15,"<>"&"")

returns

{1; 1; 2; 2; 2; 0; 0; 0; 1; 0; 4; 4; 4; 4}

Step 3 - Filter and calculate values

IF($C$2:$C$15="", (1/COUNTIFS(A2:A15,A2:A15,C2:C15,"<>"&"")), 0)

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE}, (1/{1; 1; 2; 2; 2; 0; 0; 0; 1; 0; 4; 4; 4; 4}), 0)

and returns

{1;1;0.5;0.5;0;0;0;0;1;0;0.25;0.25;0.25;0.25}

Step 4 - Sum values

=SUM(IF($C$2:$C$15<>"", (1/COUNTIFS(A2:A15,A2:A15,C2:C15,"<>"&"")), 0))

becomes

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

and returns 5 in cell F18.

Filter unique distinct values with a condition

Read this post: Vlookup – Return unique distinct values in excel

Highlight unique distinct values with a condition

Highlight unique distinct values with a condition

Date, conditional formatting formula

=($C2<>"")*(COUNTIFS($A$2:$A2, $A2, $C$2:$C2, "<>")=1)

No date, conditional formatting formula

=($C2="")*(COUNTIFS($A$2:$A2,$A2,$C$2:$C2,"=")=1)

Download excel *.xlsx file

Count unique distinct records using a condition.xlsx