Forum Discussion

Copper Contributor

Oct 10, 2019

Solved

Computing the sum of a range based on a separate column with variable input values.

I am trying to solve for a range of numbers based on a value I choose. For example in Column A: I have a depth values in 0.05 m increments. I want to take a depth range from Column A (d-8b to d+4b, w...

help

Twifoo
Oct 12, 2019
jaretbull
In the attached version of your file, the formula in F3 is:
=SUMIFS(F8:F1507,A8:A1507,">="&B2-(8*B3),
A8:A1507,"<="&B2+(4*B3))/
(((B2+(4*B3))-(B2-(8*B3)))/F2)
Please confirm if the foregoing formula returns your expected result.
CPTU Pile Design Sheetv1.0.xlsx1.1 MB

Twifoo

Silver Contributor

Oct 11, 2019

jaretbull

Please attach your sample file with manually entered results and the logic of each result.

jaretbull

Copper Contributor

Oct 12, 2019

Spoiler

Twifoo

Thank you for your reply.

Please find attached the sheet. Refer to Sheet2. I have included a photo in the sheet with my manual calculation.

Thanks again,

Jaret

CPTU Pile Design Sheet.xlsx1.1 MB

Twifoo
Silver Contributor
Oct 12, 2019
jaretbull
In the attached version of your file, the formula in F3 is:
=SUMIFS(F8:F1507,A8:A1507,">="&B2-(8*B3),
A8:A1507,"<="&B2+(4*B3))/
(((B2+(4*B3))-(B2-(8*B3)))/F2)
Please confirm if the foregoing formula returns your expected result.
CPTU Pile Design Sheetv1.0.xlsx1.1 MB
- jaretbull
  Copper Contributor
  Oct 13, 2019
  Thank you so much! I appreciate the help Twifoo
  - Twifoo
    Silver Contributor
    Oct 14, 2019
    You’re very much welcome!