Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
Aug 08, 2024

how to find if the person attend the session how many time?

Hi I like to find the unique number of person participated in the activities throughout the year as 1 person will attend the same activity for many days/months. the date col is running through the w...
Formulas and Functions
  • bbsin's avatar
    bbsin
    Aug 14, 2024

    Thanks both, I will try

    I also need show the email based on event & month, how many time in the month and whole year he/she attended to find the unique # attended. how to get it? Thank you


PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 12, 2024

bbsin 

Currently, this approach is only possible with Excel 365 insider beta channel.

= LET(
    distinct, UNIQUE(HSTACK(MONTH(date), event, email)),
    m, CHOOSECOLS(distinct, 1),
    e, CHOOSECOLS(distinct, 2),
    PIVOTBY(e, m, m, COUNT)
  )

First it determines the unique combinations of month, event and email present within the list.  The row  count for each combination of event and month is determined by the new PIVOTBY function.

Resources