Forum Discussion
Solved
How to reference a cell value and use it as the value of a row in a SUMIFS formula
I have the following table that I want to reference in a SUMIFS formula to determine the start and end of the rows in the sum. row/column A B C D 1 1 6 16 29 2 7 15 28 55...
You can use the INDIRECT function:
=SUMIFS(INDIRECT("'SheetA'!$D"&A$1&":$D"&A$2), INDIRECT("'SheetA'!$B"&A$1&":$B"&A$2), "PAY")
or the INDEX function:
SUMIFS(INDEX(SheetA!$D:$D, A$1):INDEX(SheetA!$D:$D, A$2), INDEX(SheetA!$B:$B, A$1):INDEX(SheetA!$B:$B, A$2), "PAY")
You can use the INDIRECT function:
=SUMIFS(INDIRECT("'SheetA'!$D"&A$1&":$D"&A$2), INDIRECT("'SheetA'!$B"&A$1&":$B"&A$2), "PAY")
or the INDEX function:
SUMIFS(INDEX(SheetA!$D:$D, A$1):INDEX(SheetA!$D:$D, A$2), INDEX(SheetA!$B:$B, A$1):INDEX(SheetA!$B:$B, A$2), "PAY")
Thank you so much this worked and saved me so much time!