Find percentage IF number present

I have a spreadsheet with 6 sheets. There are 3 sheets that have information regarding the soil, geology and drift deposits of an area. The other 3 sheets relate to these sheets to display the information for each area.

The way that the spreadhseet is set out pretty much explains what i am trying to archive. Basically I want each sheet to say whether or not the rock, soil or drift type is present for each area. And if it is present to calculate the percentage of total area.

I have created a pivot table for each of the info spreadsheets - but need it to look like the "table" sheets, because it needs to be added to a report.

Any questions and i'll try and answer them , but it's quite difficult to explain in words what i am trying to achive. So the best thing is to have a look at the spreadsheet if you think you can help out.

Cheers

I have a few of questions re: Table_Solid:

1 - presumably "Present" just means there is match of AP No. (Col A) and Header (C:E) ... ?

2 - is % at Surface meant to be the count of A at header divided by total count of header ?

3 - Your headers don't seem to tie out completely to any given column on the source sheet... am I missing something ?
eg for D1 you would need to add [Undifferentiated], no ? ... and E1 would need to change significantly...

If you can answer the above I'll endeavour to provide a solution.

Firstly, please us the new spreadsheet I have uploaded - I realised that the areas were all wrong.

1 - presumably "Present" just means there is match of AP No. (Col A) and Header (C:E) ... ?

2 - is % at Surface meant to be the count of A at header divided by total count of header ?

3 - Your headers don't seem to tie out completely to any given column on the source sheet... am I missing something ?
eg for D1 you would need to add [Undifferentiated], no ? ... and E1 would need to change significantly...

1- Yes present simply requires a yes or no in the cell

2- If present this should show the % of coverage for that particular AP No.
For instance A1, Aeron at Aberaeron road bridge:
Aberystwyth grit group - 94.8%
Llandovery Rock - 5.2%
(values from pviot table)

3- It doesn't matter so much that the headers don't match. Although i understand that this would be confusing. I understand that there are less headers than options - but I have removed the unimportant selevtions from the headers, so that only relevant information is present. Please see pivot table to illustrate this.

I hope these answers make sense - thanks so much for your efforts so far

Re: your point 3... I'm afraid it is of paramount importance that your headers in the summary sheets row 1 tie out to the values on the source sheet else you have no real means by which to sum values.

I have attached a quick example re: Table_Solid / Solid... on the latter sheet I have added a Concatenation key for you thereby avoiding the need for expensive array formulae (incl. Sumproduct).

Re: % at surface...it would make most sense really to store the total sum of Area for each category in a given cell such that you don't need to repeat the divisor calculation for each column (Lexicondes) ... I'm not sure how feasible that is for you so for the time being I've repeated the divisor calculation in each column ... wherever you duplicate a formula result you should look to store in one cell only and reference said cell in every subsequent calculation.

I am not around much today to progress this further but the same concept can be applied to the other sheets.

On an aside... if you altered your PT such EA_WB_ID was an additional DATA Field you could use an INDEX esque approach to retrieve the % directly from the PT... as is this is not possible ... I'm not sure whether you can alter the PT layout or not...