SUM

[Guest]

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

 

[Guest]

try:

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

E1=7.0 m

Assumes data strts in row 1.

 

[Roger Govier]

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

 

[David Biddulph]

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