Forum Discussion

Machala Sentance's avatar
Machala Sentance
Brass Contributor
Feb 19, 2020
Solved

Help with Formula

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 ...
excel
Formulas and Functions
  • Branislav1984's avatar
    Branislav1984
    Feb 19, 2020

    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 

Machala Sentance's avatar
Machala Sentance
Brass Contributor
Feb 19, 2020

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

Branislav1984's avatar
Branislav1984
Brass Contributor
Feb 19, 2020
Hi Machala,

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

Rgds,
Branislav
  • Machala Sentance's avatar
    Machala Sentance
    Brass Contributor
    Feb 19, 2020

    Hi Branislav

     

    You are a superstar, thanks for saving my sanity

     

    Kind regards

     

    Machala

    • Branislav1984's avatar
      Branislav1984
      Brass Contributor
      Feb 19, 2020
      Glad I could help 🙂

      Best regards,
      Branislav

Resources