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 Serge and Harun

thank you for your efforts but I am a bit confused. Tried both your formulas and ended with different results. Since I dont know the exact answer, I dont know which one of those 2 formulas are accurate.

When I wrote:
=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 )
)

I got result 30

when I wrote:

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

I got result 31.

Can you please help understand this difference?