Forum Discussion

jaretbull's avatar
jaretbull
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, 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! 

  • Twifoo's avatar
    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. 

5 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor

    jaretbull 

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

      • Twifoo's avatar
        Twifoo
        Silver Contributor

        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. 

Resources