sumif across sheets sans MoreFunc

D

Dave Patton

Well-known Member

Joined

Feb 15, 2002

Messages

6,484

Office Version

1. 365
2. 2010

Platform

1. Windows

• Jun 5, 2003

• #2

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.

 

Upvote 0

Corticus

Well-known Member

Joined

Apr 30, 2002

Messages

1,579

• Jun 5, 2003

• #3

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!

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jun 5, 2003

• #4

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")))

 

Upvote 0

Corticus

Well-known Member

Joined

Apr 30, 2002

Messages

1,579

• Jun 5, 2003

• #5

Perfect Aladin,

You did it again!

Thanks,

 

Upvote 0