- BY Kevin Lehrbass
- POSTED IN Efficiency, Reviews
- WITH 0 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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