Dynamic sumif cell range to include all cells above active cell

0
votes

I'm looking for a way to identify ranges in a sumif formula in Google Sheets where the range includes all cells above the active cell.

I want my formula ranges to start at row 5 then count all cells below row 5 until either the active row or the criteria "Grand Total" so the sumif formula knows where the range ends. I'm trying:

=sumif(A5:A&match("Grand Total",A5:A,0),{"David Total","Jill Total"},F5:F&(match("Grand Total",A5:A,0)))

the match formula should result in the criteria range A5:A24 and sum range F5:F24 but I'm getting a message "argument must be a range".

1
google-sheetssumgoogle-sheets-formulaarray-formulas

1 Answers

0
votes

try it like this:

=ARRAYFORMULA(SUMIF(
 INDIRECT("A5:A"&MAX(IF(A5:A="Grand Total", ROW(A5:A), ))),
 {"David Total", "Jill Total"},
 INDIRECT("F5:F"&MAX(IF(A5:A="Grand Total", ROW(A5:A), )))))

0