sumif across sheets sans MoreFunc

[Corticus]

Hi all,

Any suggestion on how to do a SumIf() on multiple sheets (same range). I don't want to use Threed() because it requires MoreFunc which my users will most likely not have.

Here's what the formula should do:
=SUMIF(July:Final!$C$63:$GT$63,"=" & D2,July:Final!$C65:$GT65)

Thanks!

 

[Dave Patton]

my formula includes sheets named 1,2,3,etc

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:2"))&"'!C63:GT63"),"="&D2,INDIRECT("'"&ROW(INDIRECT("1:2"))&"'!C65:GT65")))

- not tested

Please advise if the formula works in your situation.

 

[Corticus]

Thanks for the reply Dave,

I couldn't get it to work, though. I'm not sure what you're trying to do with the indirect("1:2"). I got a #REF for the formula.

My sheets are name July-June, Pre-Final, Final.

Thanks!

 

[Aladin Akyurek]

Book2
	A	B	C	D	E	F
1
2		July		2
3		Final		345
4
Sheet1

B2:B3 houses the relevant sheet names.

D2 houses the criterion for summing.

D3 houses the formula (sans morefunc) that you need:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$2:$B$3&"'!C63:GT63"),D2,INDIRECT("'"&$B$2:$B$3&"'!C65:GT65")))

 

[Corticus]

Perfect Aladin,

You did it again!

Thanks,