Help Needed: Pattern Formula.

[mwfazal]

Hey Everyone.
i definitely need some help. I am making an excel sheet and I have put in a formula =SUMIFS('23.03.15'!BH$4:BH$135,'23.03.15'!$C$4:$C$135,$C10,'23.03.15'!BG$4:BG$135,$D10)..

Now the thing is When I drag it horizontally BH$4:BH$135 changes to BI$4:BI$135. What I want it is that I want it to change to BM$4:BH$135 and so on leaving 5 columns in between.. and the same with the BG$4:BG$135? Can Someone help please?

 

[Shrikant]

SUMIFS by syntax require a one-dimensional array and All the arrays in the formula must be of similar size.
So I guess what you want will not be delivered by SUMIFS.

However, Excel has surprised me frequently. So i will wait for MVPs comment here.

When you drag horizontally, do you want to jump to every 5th column or do you want to include 5 columns?

 

[mwfazal]

Jump to Every 5th Column..
I should not be too hopeful then.. :/

 

[steve the fish]

This sort of thing will produce an array from R4C60:R135C60 and will increment by 5 columns.

INDEX(4:135,,((COLUMNS($A$1:A1)-1)*5)+60)

 

[Shrikant]

Jump to Every 5th Column..

this will be possible.
Steve the fish has already given the tip.

Let me try at my end another formula

 

[mwfazal]

This sort of thing will produce an array from R4C60:R135C60 and will increment by 5 columns.

INDEX(4:135,,((COLUMNS($A$1:A1)-1)*5)+60)

I always had a problem understanding this formula. Can you explain this please? Moreover where exactly to put this formula