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

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 30, 2024

Just an excuse to use SUMPRODUCT.  I thought it was heading to obsolescence!

= LET(
    distinctnames, UNIQUE(Names),
    upcoming, SIGN(COUNTIFS(Names, distinctnames, Status, "Upcoming")),
    open, 1 - SIGN(COUNTIFS(Names, distinctnames, Status, "Closed")),
    SUMPRODUCT(upcoming, open)
  )