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]

BTW, did you see my post in the Lounge?

 

[Patience]

Just replied.

 

[pgc01]

Hi Bryony

Your formula is equivalent to

=SUMIF(I58:I157,I108,K58:K157)

simpler and non volatile. Do you have any reason to use Offset()?

 

[Patience]

The reason being... and I can see flaws in this too... is that it is to be used in a long list of data. That version (which I had been previously using) doesn't work for the top rows of data. And I need the formulae to be the same, so the list is sortable. The reson why I am not just using the whole column is purely for speed. Although... OFFSET is slow, too.

 

[pgc01]

That version (which I had been previously using) doesn't work for the top rows of data. And I need the formulae to be the same, so the list is sortable.

Well, I don't understand, but I don't have all the information and so I may be missing something. Your formula seems to be a running sum, and assuming you write any of the formulas in row 108, if you drag them up they will both stop working at row 49, as expected.

Anyway I was only mentioning the other formula because Offset() is volatile, and if you have many of them in the worksheet it can slow it down.

 

[Patience]

Yeah... I think that's what I mean. Row 108 was just an example... an odd example, but yes, at Row 49 the normal SUMIF fails. I use the OFFSET to define a range that will take that into account. (=OFFSET(I806,IF(ROW(I806)<51,-ROW(I806)+1,-50),0,100) is the whole thing.

This may be one of those situations where I have been thinking about it too long, and have come to an absurd solution to a fairly simple problem. With me, that has been known to happen!

 

[pgc01]

I see, then, if I understood correctly, the real Sumif() formula is:

=SUMIF(OFFSET(I806,IF(ROW(I806)<51,-ROW(I806)+1,-50),0,100),I806,OFFSET(K806,IF(ROW(K806)<51,-ROW(K806)+1,-50),0,100))

I'd still try a non-volatile formula, like:

=SUMIF(IF(ROWS($I$1:I806)<51,$I$1,I756):I855,I806,IF(ROWS(I$1:$K806)<51,$K$1,K756):K855)

If it's a long list, it might be worth it.

 

[Patience]

Yeah - Ok I see what that does... I hadn't thought of that. (Have thought of a million other things that don't work, though!) That looks much more efficient - I am not a fan of volatiles if I don't need to use them.

 

[Ver101]

Hi Bryony

Your formula is equivalent to

=SUMIF(I58:I157,I108,K58:K157)

simpler and non volatile. Do you have any reason to use Offset()?

can the offset function in multiple sheet, meaning summing up various data coming from multiple sheet with the same input criteria?