You will find it very difficult to create pivot tables based upon your Source table because it is already in the format of a pivot table.
Essentially, you can't easily create a summary of this data because it is already a summary.
To create the pivot tables you need, you will have to 'back track' a little so that your data is in a very raw format like this:
Once you have reformatted your Source data like this, you should find it very easy to create the pivot tables you need.
All of the skills you should need to create these pivot tables are covered in the following video lessons:
Expert Skills Lesson 5-1 Create a pivot table
Expert Skills Lesson 5-3 Understand pivot table rows and columns
I have attached an example workbook showing your data in its reformatted state and the two pivot tables you need:
I hope you're now able to create the pivot tables you need, but please feel free to reply if you need any further assistance.
Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.