Help with Formula

[Machala Sentance]

Hi

I am sure that there should be a really easy solution to this, but I have been going around in circles trying various different formulas and pivot tables.

Currently I have a data download that shows every student, and what they have been attending each week.

What  I need to be able to do is look up the module name, the activity and count the number of "Y" for each student over the total number of weeks, so that I can have an overview/summary of how many have attended.

I have attached a quick snapshot book, but the data it's self is about 10,000 rows.

Thanks

Machala

[#2 · Accepted solution]

Hi @Machala Sentance 

I don't know if I understood properly but please check attached file and see if it helps.

Your original data was not suitable for Pivot table so I used PowerQuery to unpivot it (check UNPIVOTED DATA sheet) and then generated from it a Pivot table that may help you. 

If you check filter field in the PIvot you will see that you can filter between "Y", "N" depending on what you need to count.

BR,

Branislav 

[#3]

Hi Branislav

Thanks for your reply, data sort of works, apologies for not making it clearer.  I am trying to add up the total number of "Y" for each activity and module for example if 2 students attended the lecture for archery that would be 2, but if 3 attended the tutorial for archery this would be 3

Thanks

Machala

[#4]

Hi Machala,

Ok, pull "studenthostkey" out of Rows field in Pivot. It should give the view you described.

Rgds,
Branislav

[#5]

Hi Branislav

You are a superstar, thanks for saving my sanity

Kind regards

Machala

[#6]

Glad I could help :)

Best regards,
Branislav