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:

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

<tbody>
</tbody>

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

Depth	Unit	Sequence	Row thickness	Cumulative sequence thickness	Sequence thickness	Basinal shales	Offshore shelf	Prograding shoreface	Tidal channels	Palaeosols	Rivers
x	Basinal Shales and Hemi-Pelagic Fines	J10	=C3-C2	=IF($E2<>$E1,$F2,$F2+$G1)	=IF($E2<>$E3,$G2,"")	=IF($D2="Basinal Shales and Hemi-Pelagic Fines",$F2,"")	=IF($D2="Offshore Transition and Offshore Shelf",$F2,"")	=IF($D2="Prograding Shoreface",$F2,"")	=IF($D2="Tidal channels and inter-tidal to sub-tidal flats",$F2,"")	=IF($D2="Palaeosols and Floodplain",$F2,"")	=IF($D2="Fluvio-distributary channels",$F2,"")

<tbody>
</tbody>

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()))))