Excel SUMIF variations

Question

I'm trying to make a SUMIF function like this one =SUMIF(OUT!$G$22:$G$70;'2018'!$B4;OUT!$J$22:$J$70)

But i would like instead of giving the name of the sheet OUT, i want the excel to read de name of the sheet from an other cell like i show on the img.

I tried =SUMIF(CELL("contents";E2)!$G$22:$G$70;'2018'!$B4;CELL("contents";E2)!$J$22:$J$70)

but still not working. Anyone knows how to do it?

Excel image

Answer 1

You need INDIRECT:

=SUMIF(INDIRECT("'"&E2&"'!G22:G70");'2018'!$B4;INDIRECT("'"&E2&"'!J22:J70"))

Note that since the ranges are literal text strings, you don't need to make them absolute since they won't adjust if you copy/fill anyway.