Forum Discussion

sameryamak's avatar
sameryamak
Copper Contributor
Nov 29, 2024
Solved

new people only from array

Hi Everyone Suppose you have the list below: names and status. Status may change from upcoming to closed with time meaning status is dynamic.  I am looking for a function to return the count of new...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 30, 2024

    As variant

    =LET(
  names,      A2:A13,
  status,       B2:B13,
  upcoming, FILTER(names, status="Upcoming"),
  closed,       FILTER(names, status="Closed"),
  SUM( ISNA( XMATCH( upcoming, closed ) )*1 )
)

     

Harun24HR's avatar
Harun24HR
Silver Contributor
Nov 30, 2024

Unique count should be 2 as Peter is in Closed list (as per your screenshot). Try the following formula-

=COUNTA(UNIQUE(FILTER(A2:A13,COUNTIFS(A:A,A2:A13,B:B,"Closed")=0)))