SUMIF Formula

[angie07]

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, 

[#2]

@angie07 

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

[#3]

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.

[#4 · Accepted solution]

@angie07 

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

=LET(
    data, 'Tab 1'!$AD$6:$AP$1317,
    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)
)

[#5]

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

[#6]

You're welcome! Glad to help.