This is a cruel Excel challenge (I’ve used it!). Why? Because there is a brilliantly simple solution to summing cells that overlap two ranges.
The Challenge
Sum cells that are found in both ranges!
Cells found in both ranges are highlighted green (using conditional formatting).
The EASY Solution!
=SUM(C10:G24 E3:I14)
Include both ranges with a space (NOT a comma) between them!
The space tells Excel we want the intersection of the ranges. Other functions (Min, Max) also work.
Excel File
Sheet ‘CHALLENGE’ compares the easy solution with a tedious/complex solution.
Sheet ‘MORE FUN!’ is proof that I’m a Excel nerd 🙂 Use arrows to change range dimensions. I used formulas inside conditional formatting to create range borders and color overlapping cells green.
Book Behind This Trick
I learned this trick from Paul McFedries book ‘Formulas and Functions’
I’ve read 2003 & 2010 editions. This is the current edition.
If you are past the basics then I highly recommend this book! I’ve learned so much from this book and I review it often.
The beginning chapters include some essential basic tips and concepts. Further on you’ll find advanced concepts:
- Chapter 12 Working with Statistical Functions
- Chapter 16 Using Regression to Track Trends and Make Forecasts
- Chapter 17 Solving Complex Problems with Solver
- Chapter 19 Building Investment Formulas
About Me
My name is Kevin Lehrbass. I’m a Data Analyst.
Yes…I challenged someone a few years ago to solve this. I could see the smoke rising from the effort. The solution worked but was complex.
Upon seeing the easy solution I detected a “you got me” gaze followed by awkward silence. I won’t use this challenge again.