Forum Discussion

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

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 )
)

SergeiBaklan

Diamond Contributor

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

Silver Contributor

Nov 30, 2024

SergeiBaklanI think you missed UNIQUE() function as OP wants to count each person only one time. So, formula would may be-

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

SergeiBaklan
Diamond Contributor
Nov 30, 2024
Harun24HR , yes, thank you for the correction