Forum Discussion

RMillarBA's avatar
RMillarBA
Copper Contributor
Mar 14, 2023

Sum Value of Multiple Ranges

Is there a formula/combo of formulas to use the depth ranges (blue) to sum the fractures (green) based on the depths (yellow)? Thanks!

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Your question is not clear. Explain little more. What would be your desired output?
    • RMillarBA's avatar
      RMillarBA
      Copper Contributor
      Run No. 45 for example, I would like to take the range 135.5-140, search/match the yellow range 135.5-140.5 and sum the freqfeet associated with it. Something similar to sum(xlookup()) but with a range.
      Thanks.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        RMillarBA 

        =SUMPRODUCT(INDIRECT("C"&MATCH(1,($A$2:$A$12<=E2)*($B$2:$B$12>=E2),0)+1):INDIRECT("C"&MATCH(1,($A$2:$A$12<=F2)*($B$2:$B$12>=F2),0)+1))

        Does this formula return the expected results in the simplified example? The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.

         

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    RMillarBA 

    The totals are bit low but that may be expected. I'm using the actual data you provided in the screen cap.

    =LET(filtered,FILTER(freq_feet,(E2>=top_depth)*(F2<=bot_depth),0),SUM(filtered))

     

Resources