Using OFFSET in SUMIF

[Patience]

Dear all, it must be Friday as I am bamboozled by OFFSET...

I want to use OFFSET in a SUMIF to define the sum range argument...

Essentially this is the OFFSET (simplified) =OFFSET(K108,-50,0,100) (which when I put a SUM around, works.

But, when I put it as the sum_criteria, I get a blank result. =SUMIF(OFFSET(I108,-50,0,100),I108,OFFSET(K108,-50,0,100))

What I want it to do is look at all the cells from 50 above I108 to 50 below I108, and sum the corresponding cells in colum K.

Makes sense?

Thanks,

 

[xld]

Array-enter it.

 

[Sandeep Warrier]

Dunno why you are facing a problem..

Works fine for me.

Excel Workbook
	D	E
22	ABC	100
23	DEF	200
24	GHI	100
25	ABC	300
26	DEF	210
27	GHI	510
28	ABC	310
29	DEF	420
30	GHI	100
31
32	830
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D32	=SUMIF(OFFSET(D26,-4,0,9),D26,OFFSET(E26,-4,0,9))

 

[Patience]

You. Are brilliant. Thank you so much!

 

[xld]

Or use SUMPRODUCT

=SUMPRODUCT(--(OFFSET(I108,-50,0,100)=I108),OFFSET(K108,-50,0,100))

 

[Patience]

Do you know which is more... processor heavy, by any chance?

 

[xld]

In my tests the SUMIF is a good 20% faster.

 

[Patience]

Great - Thanks again. Thanks for properly testing, too. I didn't mean you to go to that trouble.

 

[xld]

It's no trouble with FastExcel, just a copy paste and click a button a few times

 

[Patience]

Yeah - I remember FastExcel - must get me some of that action!