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!
excel
Formulas and Functions
Harun24HR's avatar
Harun24HR
Bronze Contributor
Mar 15, 2023
Your question is not clear. Explain little more. What would be your desired output?
  • RMillarBA's avatar
    RMillarBA
    Copper Contributor
    Mar 15, 2023
    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
      Mar 15, 2023

      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.

       

Resources