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

Harun24HR

Silver Contributor

Nov 30, 2024

First apply both formula to your sample data as shown on screenshot. Also, you may test on my Excel sheet that I have attached to my answer. Then observe result you got and compare which formula gibe you correct result. Then apply the correct formula to your actual dataset.

sameryamak
Copper Contributor
Nov 30, 2024
thank you Harun for your concern.
Good idea. Tried your trick. Got 30 in your formula. I guess now the ansers are aligned. thanks much again
- Harun24HR
  Silver Contributor
  Nov 30, 2024
  So which one correct formula?