You could use Address...
However you'd have to do it twice becaues address only returns a single cell
so you'd have 1 for F1 then concatenate ":" then another address for F10000

Try offset..

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$4&"'!c1:c10000"),$C8,OFFSET(INDIRECT("'"&$A$2:$A$4&"'!f1:f10000"),0,COLUMNS($A:A)-1)))