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

[bbsin]

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 whole date by mm/dd/yy for the activities

Can it show by month and also by whole year to display the activities, staff type , and unique person attended in number?

Date	Activities	Staff Type	email
	A	full Time
	B	Contract
	C	Part Time
	D
	E

Thank you

[#2]

@bbsin 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

hi

I have attached the file

Thank you

[#4]

@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.

[#5]

@bbsin 

Create a pivot table based on the data, and add the data to the Data Model:

First, add the Date field to the Rows area.

Right-click any date and select Group...

Select Months, then click OK.

(If your data span multiple calendar years, also tick Years)

This creates a new field Date (Month). Drag this to the Filter area.

Next, add Event to the Rows area and Type to the Columns area (or vice versa if you prefer).

Drag the Email field to the Values area.

Click on Count of Email in the Values area and select Value Field Settings...

Select Distinct Count in the Summarize value field by list. This option is only available if you added the data to the Data Model when you created the pivot table.

Click OK.

The pivot table looks like this in tabular layout:

You can select single or multiple months in the Filter drop-down.

[#6]

@bbsin 

Here is the workbook with the pivot table.

[#7 · Accepted solution]

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

[#8]

@bbsin 

I might well have got the wrong end of the stick here and calculated how many distinct email addresses appear in each category rather than how many occurrences are there of any given email address.  Nevertheless, interesting things are possible using the latest versions of Excel 365.  For example

was generated by the formula

= LET(
    ToListλ,  LAMBDA(sep, opt, LAMBDA(arr, TEXTJOIN(sep, opt, arr))),
    distinct, UNIQUE(HSTACK(TEXT(date, "mmm"), event, email)),
    m,        CHOOSECOLS(distinct, 1),
    v,        CHOOSECOLS(distinct, 2),
    e,        CHOOSECOLS(distinct, 3),
    PIVOTBY(v, m, e, ToListλ(CHAR(10), 0), , 0, , 0)
  )