Removing OFFSET from formula due to large size file

[TG2812]

Hello,

Due to a large size file, i'm attempting to remove OFFSET from the below formula (seems i cant copy the formula in this forum?)
My attempts have been unsuccessful so far. Any help would be greatly appreciated

PS: Do you know if I store today in an isolated cell and point my formula to this cell would eventually make my file run faster?

=IF(OR(YEAR(H$2)< YEAR(TODAY()),AND(YEAR(H$2)=YEAR(TODAY()),MONTH(H$2)< MONTH(TODAY()))),SUMIFS(OFFSET(Sheet4!$A:$A,,MATCH (H$2,Sheet4!$1:$1,0)-1),Sheet4!$A:$A,Main!$A7,Sheet4!$B:$B,Main!$B7),G7-H3+H4)

 

[Joe4]

It appears that your formula is being cut-off. I am guessing that you are using < or > signs, which are being mistaken for HTML code.
If you put spaces on both sides of all < and > signs, your formula should post, i.e.
=IF(a < b,...

 

[offthelip]

You can replace the OFFSET function which is a volatile fucntion with the INDEX function which is non volatile and so it should reduce the number of recalculations.

 

[Eric W]

Maybe:

<eomonth(today(),-1)+1,sumifs(index(sheet4!<font color="#ff0000">=IF(H$2< EOMONTH(TODAY(),-1)+1,SUMIFS(INDEX(Sheet4!$A:$Z,0,MATCH(H$2,Sheet4!$1:$1,0)),Sheet4!$A:$A,Main!$A7,Sheet4!$B:$B,Main!$B7),G7-H3+H4)

Change the range in red to be the maximum number of columns used on Sheet 4 (XFD if you just want to use the whole sheet). Also, since your condition looked to mean "If the date in H2 is from a month before today's month", I found a shorter way to do that.

Let us know if this works.


Also, TODAY() is volatile, but moving it to another cell won't help. All the cells pointing to that cell are dependent on it, and will recalculate when it recalculates, so you're just adding another level to it.</eomonth(today(),-1)+1,sumifs(index(sheet4!

 

[TG2812]

Thank you for the advise. Well noted!

Any idea how can I get rid of the OFFSET in this formula?

 

[TG2812]

Eric, it works like a charm. Thank you very much for your help!

 

[Eric W]

Happy to help!

Have a happy New Year!