# 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! 5 Replies

# Re: Sum Value of Multiple Ranges

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

# Re: Sum Value of Multiple Ranges

Subtotal by color

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

# Re: Sum Value of Multiple Ranges

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.

# Re: Sum Value of Multiple Ranges

``=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. # Re: Sum Value of Multiple Ranges

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