My problem is this.

In Column A I have a list of depths, (i.e. 1 - 10m in increments of 0.5m)

Column B has different levels corresponding to ground strata (i.e. Sand to
1.5m, clay below that to 4m, gravel below that to 6m etc..)

Column C has a designated shear strength value according to the type of soil.

What I want to do is sum the values in column C according to the value in
column A. For example for a depth of 7m, I want to sum all the values in
column C for the given levels in Column B between 0 and 7m

Hope this is explained well enough

try:

=SUM(OFFSET($C$1,0,0,MATCH(E1,$A$1:$A$19,0)))

E1=7.0 m

Assumes data strts in row 1.

"Blades" wrote:

My problem is this.

In Column A I have a list of depths, (i.e. 1 - 10m in increments of 0.5m)

Column B has different levels corresponding to ground strata (i.e. Sand to
1.5m, clay below that to 4m, gravel below that to 6m etc..)

Column C has a designated shear strength value according to the type of soil.

What I want to do is sum the values in column C according to the value in
column A. For example for a depth of 7m, I want to sum all the values in
column C for the given levels in Column B between 0 and 7m

Hope this is explained well enough

Hi

The answer to summing the values is
=SUMPRODUCT((A1:A20<=7)*C1:C20)

But I suspect that what you really want is the average which would be
=SUMPRODUCT((A1:A20<=7)*C1:C20)/COUNTIF(A1:A20,"<=7")

--
Regards
Roger Govier



"Blades" wrote in message
...
My problem is this.

In Column A I have a list of depths, (i.e. 1 - 10m in increments of 0.5m)

Column B has different levels corresponding to ground strata (i.e. Sand to
1.5m, clay below that to 4m, gravel below that to 6m etc..)

Column C has a designated shear strength value according to the type of
soil.

What I want to do is sum the values in column C according to the value in
column A. For example for a depth of 7m, I want to sum all the values in
column C for the given levels in Column B between 0 and 7m

Hope this is explained well enough

Look at help for the SUMIF() function.

I'm interested that you can just add the shear strengths (but my soil
mechanics theory is pretty rusty).
--
David Biddulph

"Blades" wrote in message
...
My problem is this.

In Column A I have a list of depths, (i.e. 1 - 10m in increments of 0.5m)

Column B has different levels corresponding to ground strata (i.e. Sand to
1.5m, clay below that to 4m, gravel below that to 6m etc..)

Column C has a designated shear strength value according to the type of
soil.

What I want to do is sum the values in column C according to the value in
column A. For example for a depth of 7m, I want to sum all the values in
column C for the given levels in Column B between 0 and 7m

Hope this is explained well enough