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

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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's avatar
sameryamak
Copper Contributor
Nov 30, 2024

worked like a charm, thank you so much SergeiBaklan 

  • 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?