SOLVED

New Contributor

# SUMIF Formula

Hi,

I would like to be able to drag the SUMIF formula as the criteria is the same but the sum range changes. The Sum range is available on a different excel tab and can be found after every 4 columns.

I am using absolute values for the criteria.

Thank you,

5 Replies

# Re: SUMIF Formula

Please provide more detailed, specific information, preferably with a screenshot and/or a link to a sample workbook.

# Re: SUMIF Formula

Hi Hans,
I hope this helps understand the problem -

Is it possible to drag the Sumif formula horizontally – the criteria is the same but the sum range changes and is available on a different excel tab at 4 columns interval.

If I manually enter the formula in column 1 – =SUMIFS('1) Tab 1′!AD\$6:AD\$1317,'1) Tab 1'!\$D\$6:\$D\$1317,'Store count!\$A3)

If I manually enter the formula in column 2 – =SUMIFS('1) Tab 1′!AH\$6:AH\$1317,'1) Tab 1'!\$D\$6:\$D\$1317,'Store count!\$A3)

If not, is there another embedded function that would help with this.
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: SUMIF Formula

I wrote this with compatibility in mind. If your version of Excel has FILTER, this will work.  Sample workbook included!

``````=LET(
store, 'Tab 1'!\$D\$6:\$D\$1317,
r, SEQUENCE(ROWS(data)),
c, SEQUENCE(, INT(COLUMNS(data) / 3), 1, 4),
matrix, INDEX(data, r, c),
f, FILTER(INDEX(matrix, r, COLUMN(A1)), store = \$B2),
SUM(f)
)``````

# Re: SUMIF Formula

Thanks Patrick, appreciate the help. I would have never been able to figure this out on my own.