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

sameryamak

Copper Contributor

Nov 30, 2024

hi both

I think I am confused now. I tried both formula I am getting different results

=LET(
names, Data!$C$2:$C$172,
status, Data!$I$2:$I$172,
upcoming, UNIQUE(FILTER(names, status="Upcoming")),
closed, UNIQUE(FILTER(names, status="Closed")), SUM( ISNA( XMATCH( upcoming, closed ) )*1 )
)

leads to 30

while

=COUNTA(UNIQUE(FILTER(Data!$C$2:$C$172,COUNTIFS(Data!$C$2:$C$172,Data!$C$2:$C$172,Data!$I$2:$I$172,"Closed")=0)))

leads to 31

can you please help me understand this difference? Note: I can not visually tell because the data is a lot