# Sum Cells in Overlapping Ranges

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