Forum Discussion
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 columns.
I am using absolute values for the criteria.
Thank you,
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 Replies
Please provide more detailed, specific information, preferably with a screenshot and/or a link to a sample workbook.
- 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.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) )
