Formula for unique values under a certain criteria.

Copper Contributor

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!

MontyKnoll_0-1684206363172.png

 

2 Replies

Hi @MontyKnoll 

 

If you run 365 one way:

Sample.png

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

 

 

@MontyKnoll 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

formula for unique values.JPG