Sum Value of Multiple Ranges

Copper Contributor

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


5 Replies
Your question is not clear. Explain little more. What would be your desired output?


Subtotal by color

How to count and sum cells based on background color in Excel?


Maybe these links will help you a bit further in your project.

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.



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.

Sum Value of Multiple Ranges.JPG 


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