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!

Capture.PNG

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

@RMillarBA 

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

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

Sum Value of Multiple Ranges.JPG 

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