Forum Discussion

MontyKnoll's avatar
MontyKnoll
Copper Contributor
May 16, 2023

Formula for unique values under a certain criteria.

Hi everyone!

 

I'm trying to workout a formula that counts how many unique people attend an appointment (If 1 person attends the appointment 2 times then it still only counts them as 1). But also I would like the formula to be able to count the people based on the type of appointment.

 

I made an example excel sheet to demonstrate my issue but my work doesn't allow me to send any of my spreadsheets to just anyone... Hopefully just an image of my issue could help you help me? 

 

Thanks!

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Hi MontyKnoll 

     

    If you run 365 one way:

    =LET(
      UniqBySession, CHOOSECOLS(UNIQUE(Table1),3),
      Subjects, SORT(UNIQUE(UniqBySession)),
      CountBy,  LAMBDA(session, ROWS(FILTER(UniqBySession,UniqBySession=session))),
      VSTACK(
        {"Session","# Unique Client"},
        HSTACK(Subjects, MAP(Subjects,CountBy))
      )
    )

     

     

Resources