HELP Creating a summary sheet from multiple worksheets which totals with two variables (which are existing data tables)

Question

Hi,

I am trying to create a summary top sheet for monthly worker timesheets. I want to create a summary per month (each worksheet=calendar month) which will either display the data in subtotals per two fields- Employment Type and Pay Type- or can be filtered by each of these fields (one of which currently exist in the sheets as drop-down from a data table- Employment Type)

I can easily pull the totals by Pay Type out onto the top sheet- but can't figure out how to add in the Employment Type field as either a linked variable or filter options. Screenshot of one of the monthly worksheets below. Highlighted in yellow the totals I am looking to pull out of each sheet. Column D= Employment Type Column N= Pay Type (which is at the moment just text)

I don't have in-depth knowledge, so I have tried to figure out with a Pivot Table, using consolidate and sumifs but I don't have enough knowledge to make these work. If someone can describe in plain terms I would be ever so grateful!!

TIA

Answer 1

Great!!!!!

I'm glad you found a solution in my reply

Thanks for the positive feedback

As for your question, you may refer to the videos below for more info.

Regards

Jeovany

https://www.youtube.com/watch?v=GUClkvJ7Gag

https://www.youtube.com/watch?v=QWizkfED000

https://www.youtube.com/watch?v=jPajIyavh_0

Answer 2

Hi

You may find in the link below a copy of your file with the answer to your question

https://we.tl/t-ANueXXLV3J

Regards

Jeovany

Answer 3

Hi again

You may find in the link below a copy of your file with a solution as per my understanding of your scenario and requirements.

https://we.tl/t-6Lx5orzgM3

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

If so please, provide us with more and clearer details of your scenario and goals

Regards

Jeovany

Answer 4

Hi Jeovany,

Thank you so much for all your help, I really appreciate it. Sorry I haven't been clearer with my goal, it has been difficult to describe and I should have sent you an example of the output I'm looking for sooner. Your answer is very close but I do not need an output by individuals, rather subtotal by the payment type and also by employment type for each month. I am interested in financial outputs in various subtotal rather than by ind individual. The difficulty is reporting by the two axis- one totalling by payment type (i.e. salary and furlough payments) and also by Employment Type (i.e. by Choir Director, Music Staff etc). I attach here a workbook with 'summary sheet' containing the outputs I would like to take from the monthly sheet- as well as 'January 2021' as an example monthly sheet you've seen before- the summary would report for each month for a full year.

Sorry not to have been clearer- I hope this helps communicate what I'm looking for and thanks once again for all your help.

https://www.dropbox.com/scl/fi/99tg0qucdw6h1024nqr36/Excel-example-for-Jeovany.xlsx?dl=0&rlkey=39t05e9xuxumw3tadov61lhbg 

Eilidh

Answer 5

Absolutely brilliant Jeovany, thanks so much. This is exactly what I was looking for. If it's not too much trouble, could you give me a very brief description of the technique you used to create this summary from the other sheets? Just so I have an idea for the next time I have to do something similar!

Many thanks again,

Eilidh