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

     

Harun24HR's avatar
Harun24HR
Silver Contributor
Nov 30, 2024

sameryamakPeter is both in closed list and on upcoming list. So, how the answer is 3? Would you please explain?

  • sameryamak's avatar
    sameryamak
    Copper Contributor
    Nov 30, 2024

    when I used the revised Serge formula with Harun adustment:
    =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 get answer 30

    but when I use Harun's count function like this:
    =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 get 31

     

    so now I am confused !

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 30, 2024

      And what shall be correct answer?

      • sameryamak's avatar
        sameryamak
        Copper Contributor
        Nov 30, 2024

        honestly I have no idea. I didn't do the manual checking.