SOLVED

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

Copper Contributor

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, where d is depth and b is length value) then sum the cells in Column F that correspond to this range. d and b are in cells above the data so I can adjust them to suit my specific needs. I would like a cell that has a formula that gives me the sum of the values in column F that correspond to the depth range of d-8b to d+4b (from Column A). Please, if you have experience doing something like this, provide me with some insight. Please request clarification if my question is unclear. Thanks! 

5 Replies

@jaretbull 

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

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

best response confirmed by jaretbull (Copper Contributor)
Solution

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

Thank you so much! I appreciate the help @Twifoo 

1 best response

Accepted Solutions
best response confirmed by jaretbull (Copper Contributor)
Solution

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

View solution in original post