Sumifs and sumproduct sumrange issues

Hi fellow forum users,

I'm looking for some assistance with the following dilema, I can't seem to get it implemented in to my workbook.

The Issue

I have a sheet laid out like the following:-

Description Category Jan Feb Mar Apr May ....Continues for the year....
From To 1 2 3 4 5 ......................................

Sales 1 100 101 £950 £850 £945 £990 £1,100
Sales 2 102 105 £220 £120 £320 £192 £350
Sales 3 106 112 £320 £256 £147 £241 £233

I need to be able to sum all the months from a specified number along with the ability to filter out only relevant items that fall within the category range..

For example:-

I will have another cell that will define the month number and a from/to category with the idea it will only sum the items that fit in that criteria, let's say in this case I'm looking to sum anything with a category between 100 - 105 and all values up to month 2 (February).

In this case the answer returned should be £950+£850+£220+£120. I have tried using sumproduct and sumifs, however the formula breaks down when I try to apply a sum range over multiple columns.

Any help would be much appreciated.

Thanks again

Stuart

Yes no problem, I have created a quick sample workbook.

Try:

Formula:

Please Login or Register  to view this content.

@InvisibleMan

What does the *-- bit mean?

Try

=SUMPRODUCT(--($B$4:$B$15>=$B$21)*--($C$4:$C$15<=$B$22)*($D$4:$O$15)*($D$3:$O$3<=$B$18))

OP wants CUMULATIVE months ... See example he quotes ..

all values up to month 2 (February).

Hi Guys,

Spot on, absolutely nailed it.

Thanks again.