Count # unique values of column if all values in corresponding values are identical-CodePudding

I'm looking to have E2 and E3 count the number of unique entries in column B based upon met criteria in column A.

Complete (E2) would count all of unique entry Address1 in column B only if any status of Address1 is set to Cancelled or Installed. Pending (E3) would count all of unique entry Address2 and unique entry Address 3 only if all corresponding status' are Pending.

CodePudding user response：

With Office 365 we can use nested Filters and Match to return the list of items that meet the necessary criteria those with and without anything but Pending. Then we use UNIQUE to make the list unique and COUNTA to count the number of returns:

For Completed:

=LET(itm,B2:B10,sts,A2:A10,unq,(UNIQUE(FILTER(itm,ISNUMBER(MATCH(itm,FILTER(itm,sts<>"Pending"),0))))),IF(ISERROR(@unq),0,COUNTA(unq)))

For Pending:

=LET(itm,B2:B10,sts,A2:A10,unq,(UNIQUE(FILTER(itm,ISERROR(MATCH(itm,FILTER(itm,sts<>"Pending"),0))))),IF(ISERROR(@unq),0,COUNTA(unq)))

CodePudding user response：

Formula I used in E2

 =SUM(--(UNIQUE(FILTER($B$2:$B$10,($A$2:$A$10="Cancelled") ($A$2:$A$10="Installed")))<>""))

In E3 formula is

=SUM(--(UNIQUE(FILTER($B$2:$B$10,($A$2:$A$10="Pending")))<>""))