Forum Discussion

Copper Contributor

Nov 25, 2022

Solved

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 colum...

excel

Formulas and Functions

office 365

Patrick2788

Nov 26, 2022

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)
)

Formula 11.26.xlsx114 KB

angie07

Copper Contributor

Nov 26, 2022

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.

Patrick2788

Silver Contributor

Nov 26, 2022

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)
)

Formula 11.26.xlsx114 KB

angie07
Copper Contributor
Nov 26, 2022
Thanks Patrick, appreciate the help. I would have never been able to figure this out on my own.
- Patrick2788
  Silver Contributor
  Nov 26, 2022
  You're welcome! Glad to help.