Forum Discussion
RMillarBA
Mar 14, 2023Copper Contributor
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!
- Harun24HRBronze ContributorYour question is not clear. Explain little more. What would be your desired output?
- RMillarBACopper ContributorRun 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.- OliverScheurichGold Contributor
=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.
- NikolinoDEGold Contributor
How to count and sum cells based on background color in Excel?
Maybe these links will help you a bit further in your project.
- Patrick2788Silver Contributor
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))