[Solved] Autofill formulas, exclude the auto fill for certain cells

[hajjash]

hello,

I have this formula:

=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B36,'Calendar Tracker'!H2:H1517,"<>")

I wan to autofill it vertically, so that only &B36 increases, not the remaining cells, so the next cell would be:

=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B37,'Calendar Tracker'!H2:H1517,"<>")
=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B38,'Calendar Tracker'!H2:H1517,"<>")
=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B39,'Calendar Tracker'!H2:H1517,"<>")
=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B40,'Calendar Tracker'!H2:H1517,"<>")

and so on. How can I do this?

Thanks!

 

[shamimarita]

hello,

I have this formula:

=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B36,'Calendar Tracker'!H2:H1517,"<>")

I wan to autofill it vertically, so that only &B36 increases, not the remaining cells, so the next cell would be:

=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B37,'Calendar Tracker'!H2:H1517,"<>")
=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B38,'Calendar Tracker'!H2:H1517,"<>")
=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B39,'Calendar Tracker'!H2:H1517,"<>")
=SUMIFS('Calendar Tracker'!O2:O1517, 'Calendar Tracker'!B2:B1517, "="&B40,'Calendar Tracker'!H2:H1517,"<>")

and so on. How can I do this?

Thanks!

Hello Hajjash,

I hope you are doing well. You can use the absolute reference if you don't want the autofill or auto increase of rows and columns.
Here is the updated formula.

=SUMIFS('Calendar Tracker'!$O$2:$O$1517, 'Calendar Tracker'!$B$2:$B$1517, "="&$B36,'Calendar Tracker'!$H$2:$H$1517,"<>")

 

[hajjash]

Hello Hajjash,

I hope you are doing well. You can use the absolute reference if you don't want the autofill or auto increase of rows and columns.
Here is the updated formula.

=SUMIFS('Calendar Tracker'!$O$2:$O$1517, 'Calendar Tracker'!$B$2:$B$1517, "="&$B36,'Calendar Tracker'!$H$2:$H$1517,"<>")

Thank you very much!