Formula without volatile formulation

[BRISBANEBOB]

Is it possible to achieve the formula neededd in the sample spreadsheet (Excel 2007) without using something like =sumproduct? I have a huge spreadsheet and anything volatile will kill the speed. I'm probably missing something really simple...

Thanks for any ideas

[JBeaucaire]

In L7 copied down and across, formatted as General afterward:

=SUMIF($B$6:$I$6, ">="&L$5, $B7:$I7) - SUMIF($B$6:$I$6, ">"&L$6, $B7:$I7)

[BRISBANEBOB]

Very clever - and it only took me 30 minutes to work out how it works!

Thanks - much appreciated and your reputation enhanced.

[JBeaucaire]

Just a note, the REAL problem with SUMPRODUCT() comes from evaluating HUGE ranges, and then putting a bunch of those huge-range formulas into a lot of cells.

With each SUMPRODUCT() only calculating a set # of cells per row, I'm not entirely sure it would be a huge hit to use it. Worth a simple test to see.

=SUMPRODUCT(--($B$6:$I$6>=L$5),--($B$6:$I$6>L$6), $B7:$I7)

[BRISBANEBOB]

I see what you mean. The first =sumproduct I ran was with a big range and not just the line totals which is what I ended up doing. Still, I prefer your =sumif formula because it's easier for those who don't use =sumproduct if they need to fix something.

Thanks

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.