Variable numbers of rows, multiple columns (some blank cells): require summation based on values in cells in one column

[AdamBB]

In order to simplify this question, I have put column headings in "quotation marks".

I am working on geological borehole log data, with "depth" in column C, a set group of "geological units" being repeated down column D, and "sequences" based on specific depositional times and environments (column E).

Column A contains the "Well ID". Each well has 20 - 30 row entries. There are potentially hundreds of wells.

"Geological units" can occur in several, different "sequences"; and one "sequence" can contain several, different "geological units".

Here is a table showing roughly what is happening so far:

[TABLE="width: 1735"]
<tbody>[TR]
[TD]Depth_ft
[/TD]
[TD]Unit
[/TD]
[TD]Sequence
[/TD]
[TD]Row_Thickness
[/TD]
[TD]Cumulative sequence thickness[/TD]
[TD]Sequence_Thickness
[/TD]
[TD]Basinal_Shales_and_Hemi-Pelagic_Fines
[/TD]
[TD]Offshore_Transition_and_Offshore_Shelf
[/TD]
[TD]Prograding_Shoreface
[/TD]
[TD]Palaeosols_and_Floodplain
[/TD]
[TD]Tidal_channels_and_inter-tidal_to_sub-tidal_flats
[/TD]
[TD]Fluvio-distributary_channels
[/TD]
[/TR]
[TR]
[TD]13662.7
[/TD]
[TD]Basinal Shales and Hemi-Pelagic Fines
[/TD]
[TD]J10
[/TD]
[TD]49.7
[/TD]
[TD]49.7
[/TD]
[TD]49.7
[/TD]
[TD]49.7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13712.4
[/TD]
[TD]Basinal Shales and Hemi-Pelagic Fines
[/TD]
[TD]J9
[/TD]
[TD]43.1
[/TD]
[TD]43.1
[/TD]
[TD]43.1
[/TD]
[TD]43.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13755.5
[/TD]
[TD]Basinal Shales and Hemi-Pelagic Fines
[/TD]
[TD]J8
[/TD]
[TD]76.6
[/TD]
[TD]76.6
[/TD]
[TD]76.6
[/TD]
[TD]76.6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13832.1
[/TD]
[TD]Basinal Shales and Hemi-Pelagic Fines
[/TD]
[TD]J7
[/TD]
[TD]216.7
[/TD]
[TD]216.7
[/TD]
[TD]216.7
[/TD]
[TD]216.7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14048.8
[/TD]
[TD]Basinal Shales and Hemi-Pelagic Fines
[/TD]
[TD]J6
[/TD]
[TD]62.8
[/TD]
[TD]62.8
[/TD]
[TD]62.8
[/TD]
[TD]62.8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14111.6
[/TD]
[TD]Basinal Shales and Hemi-Pelagic Fines
[/TD]
[TD]J5
[/TD]
[TD]27.2
[/TD]
[TD]27.2
[/TD]
[TD][/TD]
[TD]27.2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14138.8
[/TD]
[TD]Offshore Transition and Offshore Shelf
[/TD]
[TD]J5
[/TD]
[TD]393.8
[/TD]
[TD]421
[/TD]
[TD]421
[/TD]
[TD][/TD]
[TD]393.8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14532.6
[/TD]
[TD]Offshore Transition and Offshore Shelf
[/TD]
[TD]J4
[/TD]
[TD]203.4
[/TD]
[TD]203.4
[/TD]
[TD]203.4
[/TD]
[TD][/TD]
[TD]203.4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14736
[/TD]
[TD]Prograding Shoreface
[/TD]
[TD]J3
[/TD]
[TD]44.9
[/TD]
[TD]44.9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]44.9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14780.9
[/TD]
[TD]Prograding Shoreface
[/TD]
[TD]J3
[/TD]
[TD]50.9
[/TD]
[TD]95.8
[/TD]
[TD]95.8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]50.9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14831.8
[/TD]
[TD]Palaeosols and Floodplain
[/TD]
[TD]J2
[/TD]
[TD]22
[/TD]
[TD]22
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14853.8
[/TD]
[TD]Tidal channels and inter-tidal to sub-tidal flats
[/TD]
[TD]J2
[/TD]
[TD]57.1
[/TD]
[TD]79.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]57.1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14910.9
[/TD]
[TD]Palaeosols and Floodplain
[/TD]
[TD]J2
[/TD]
[TD]77.8
[/TD]
[TD]156.9
[/TD]
[TD]156.9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]77.8
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14988.7
[/TD]
[TD]Palaeosols and Floodplain
[/TD]
[TD]J1
[/TD]
[TD]17.7
[/TD]
[TD]17.7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17.7
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15006.4
[/TD]
[TD]Tidal channels and inter-tidal to sub-tidal flats
[/TD]
[TD]J1
[/TD]
[TD]27.2
[/TD]
[TD]44.9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27.2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15033.6
[/TD]
[TD]Palaeosols and Floodplain
[/TD]
[TD]J1
[/TD]
[TD]13.5
[/TD]
[TD]58.4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13.5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15047.1
[/TD]
[TD]Fluvio-distributary channels
[/TD]
[TD]J1
[/TD]
[TD]15.2
[/TD]
[TD]73.6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15.2
[/TD]
[/TR]
[TR]
[TD]15062.3
[/TD]
[TD]Palaeosols and Floodplain
[/TD]
[TD]J1
[/TD]
[TD]93.8
[/TD]
[TD]167.4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]93.8
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15156.1
[/TD]
[TD]Fluvio-distributary channels
[/TD]
[TD]J1
[/TD]
[TD]12.1
[/TD]
[TD]179.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12.1
[/TD]
[/TR]
[TR]
[TD]15168.2
[/TD]
[TD]Palaeosols and Floodplain
[/TD]
[TD]J1
[/TD]
[TD]16.6
[/TD]
[TD]196.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16.6
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15184.8
[/TD]
[TD]Tidal channels and inter-tidal to sub-tidal flats
[/TD]
[TD]J1
[/TD]
[TD]18
[/TD]
[TD]214.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15202.8
[/TD]
[TD]Fluvio-distributary channels
[/TD]
[TD]J1
[/TD]
[TD]12.1
[/TD]
[TD]226.2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12.1
[/TD]
[/TR]
[TR]
[TD]15214.9
[/TD]
[TD]Palaeosols and Floodplain
[/TD]
[TD]J1
[/TD]
[TD]36
[/TD]
[TD]262.2
[/TD]
[TD]262.2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]36
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and here is a sample row, showing the equations in place:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Depth[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Sequence[/TD]
[TD="align: center"]Row thickness[/TD]
[TD="align: center"]Cumulative sequence thickness[/TD]
[TD="align: center"]Sequence thickness[/TD]
[TD="align: center"]Basinal shales[/TD]
[TD="align: center"]Offshore shelf[/TD]
[TD="align: center"]Prograding shoreface[/TD]
[TD="align: center"]Tidal channels[/TD]
[TD="align: center"]Palaeosols[/TD]
[TD="align: center"]Rivers[/TD]
[/TR]
[TR]
[TD="align: center"]x[/TD]
[TD="align: center"]Basinal Shales and Hemi-Pelagic Fines[/TD]
[TD="align: center"]J10[/TD]
[TD="align: center"]=C3-C2[/TD]
[TD="align: center"]=IF($E2<>$E1,$F2,$F2+$G1)[/TD]
[TD="align: center"]=IF($E2<>$E3,$G2,"")[/TD]
[TD="align: center"]=IF($D2="Basinal Shales and Hemi-Pelagic Fines",$F2,"")[/TD]
[TD="align: center"]=IF($D2="Offshore Transition and Offshore Shelf",$F2,"")[/TD]
[TD="align: center"]=IF($D2="Prograding Shoreface",$F2,"")[/TD]
[TD="align: center"]=IF($D2="Tidal channels and inter-tidal to sub-tidal flats",$F2,"")[/TD]
[TD="align: center"]=IF($D2="Palaeosols and Floodplain",$F2,"")[/TD]
[TD="align: center"]=IF($D2="Fluvio-distributary channels",$F2,"")[/TD]
[/TR]
</tbody>[/TABLE]

I need to have a single row per "sequence" within each well, with a thickness value for each "geological unit" within that "sequence". e.g. for "sequence" value J1, I need to end up with a single row that totals up the thickness of each "unit" so that each unit can be expressed as a percentage of the total "sequence" thickness.

NB: each borehole has different instances of "sequence", etc. so a static equation won't work.

The data are confidential, so I am unable to upload the dataset.

Thank you for considering this post and I look forward to hearing from you!

 

[AdamBB]

In the absence of any feedback I eventually found a solution, which was to define the number of rows for each "sequence" (new Column A) then regard the data in column P (for example) that lists the individual thicknesses noted.

Then the following statement summed the figures, regardless of how many rows each "sequence" occupied:

=IF($A2="","",SUM(INDIRECT(CONCATENATE("P",ROW()-$A2+1,":","P",ROW()))))