Forum Discussion
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 |
I want to use a SUMIFS formula such as SUMIFS(SheetA!$DX:$DY, SheetA!$BX:$BY, "PAY")
Where X = references the value of cell A$1
Where y = references the value of cell A$2
Is there a way to do this? Excel newbie here and I appreciate all the help
Thanks!
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!
