Nov 25 2022 01:52 PM
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,
Nov 26 2022 02:55 AM
Please provide more detailed, specific information, preferably with a screenshot and/or a link to a sample workbook.
Nov 26 2022 08:22 AM
Nov 26 2022 10:36 AM
SolutionI 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)
)
Nov 26 2022 12:48 PM
Nov 26 2022 10:36 AM
SolutionI 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)
)