XLOOKUP - copying formula horizontally - while skipping certain columns

Hi!

I've run into a problem. I want to save time but i can't seem to figure out a solution yet.

I have a document with multiple sheets

On SHEET2, in B3, my formula that works great is =SUM(XLOOKUP(A3, SHEET1!A4:A103,SHEET1!B4:B103))

but i want to copy it across horizontally without having to change any information (i have columns going to BQZ on SHEET1)

so on SHEET2 C3 I want the formula to say =SUM(XLOOKUP(A3, SHEET1!H4:H103,SHEET1!I4:I103))
so moving over 7 spaces each time

is there a way that i can do this so i dont have to manually adjust this formula 365 times!

Thank you ahead of time if you are able to help

Please try on Sheet2 in B3 and copy to the right:

Formula:

Please Login or Register  to view this content.

The SUM function adds nothing to the formula, because XLOOKUP ($A4,...) always returns only 1 value (or error).

In addition, there is a good chance that what you want to achieve is also possible with a more efficient SUM or SUMPRODUCT without XLOOKUP and INDEX.
This depends on what is in the column headings (and what exactly you want).