SUMIF across sheets without INDIRECT

[mgirvin]

Dear Smartest Excelers In The World,

I am wondering if there is a way to sum with criteria across sheets without using INDIRECT.

Here is my data and formula:

Excel Workbook
	A	B
1	ID	Units
2	ID102	3
3	ID100	3
4	ID102	1
5	ID102	4
6	ID101	5
7	ID102	10
8	ID100	5
9	ID100	8
10	ID101	3
11	ID101	1
Jan

Excel Workbook
	A	B
1	ID	Units
2	ID101	8
3	ID100	7
4	ID102	4
5	ID101	8
6	ID102	6
7	ID101	10
8	ID101	4
9	ID102	10
10	ID102	10
11	ID102	2
Feb

Excel Workbook
	A	B
1	ID	Units
2	ID101	3
3	ID102	7
4	ID102	5
5	ID102	6
6	ID100	7
7	ID100	6
8	ID100	1
9	ID100	1
10	ID101	2
11	ID102	5
Mar

Excel Workbook
	A	B	C
1	Sheet Names		Crtieria
2	Jan		ID101
3	Feb		TOTAL
4	Mar		44
Sum

Cell Formulas
Range	Formula
C4	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A11"),C2,INDIRECT("'"&A2:A4&"'!B2:B11")))

 

[jeffreybrown]

Hi Mike,

I suppose you would like to steer clear of a Sumif on every sheet

=SUMIF($A$2:$A$11,Sum!C2,$B$2:$B$11)

and then use

=SUM(Jan:Mar!D1)

on the Sum tab.

I've never seen it without Indirect, but I suppose I will find out soon there is a way

 

[Dave Patton]

Use a UDF or Add-in such as Morefunc

A blank sheet named "Start" is before the first relevant data sheet and a blank sheet called "End" is after the last relevant data sheet.

The formula could be

=SUMPRODUCT(--(THREED(Start:End!A2:A11)=C2),(THREED(Start:End!B2:B11)))

For additional data sheets, just insert them between Start and End.

 

[mgirvin]

Thanks jeffreybrown & Dave Patton!

jeffreybrown, I can never get 3-D cell references to work with array criteria, though.

Dave Patton, I have not been able to get my MoreFunc add-in to work with Excel 2010. I love the MoreFunc add-in and used to use it all the time !!!

Does anyone know how to get the MoreFunc add-in to work in Excel 2010?

 

[Aladin Akyurek]

Thanks jeffreybrown & Dave Patton!

jeffreybrown, I can never get 3-D cell references to work with array criteria, though.

Calculating a SumIf formula per sheet, using a criteria cell from the destination sheet, combined with a regular 3d sum, is indeed a good idea.
Many closed book calculations would be much easier and less costly with the strategy: Calculate locally, Access globally.

Dave Patton, I have not been able to get my MoreFunc add-in to work with Excel 2010. I love the MoreFunc add-in and used to use it all the time !!!

Does anyone know how to get the MoreFunc add-in to work in Excel 2010?

There is no 64 bit version. See the recent discussion in:

http://www.mrexcel.com/forum/showthread.php?t=543225

 

[mgirvin]

Thanks, Aladin!

I have 64 bit and so I am still sad about no MoreFunc.



What I meant by '3-D cell references to work with array criteria' was a formula like this:

=SUM(--('Jan:Mar'!A2:A11=C2))



Your idea about:

'Calculating a SumIf formula per sheet, using a criteria cell from the destination sheet, combined with a regular 3d sum... Calculate locally, Access globally'

is great. I never thought of that. By that do you mean something like this:

Data on sheets and 'local calculate':

Excel Workbook
	A	B	C	D
1	ID	Units		SUM with Criteria from Sheet 'SUM'
2	ID102	3		9
3	ID100	3
4	ID102	1
5	ID102	4
6	ID101	5
7	ID102	10
8	ID100	5
9	ID100	8
10	ID101	3
11	ID101	1
Jan

Cell Formulas
Range	Formula
D2	=SUMIF(A2:A11,Sum!C2,B2:B11)

Excel Workbook
	A	B	C	D
1	ID	Units		SUM with Criteria from Sheet 'SUM'
2	ID101	8		30
3	ID100	7
4	ID102	4
5	ID101	8
6	ID102	6
7	ID101	10
8	ID101	4
9	ID102	10
10	ID102	10
11	ID102	2
Feb

Cell Formulas
Range	Formula
D2	=SUMIF(A2:A11,Sum!C2,B2:B11)

Excel Workbook
	A	B	C	D
1	ID	Units		SUM with Criteria from Sheet 'SUM'
2	ID101	3		5
3	ID102	7
4	ID102	5
5	ID102	6
6	ID100	7
7	ID100	6
8	ID100	1
9	ID100	1
10	ID101	2
11	ID102	5
Mar

Cell Formulas
Range	Formula
D2	=SUMIF(A2:A11,Sum!C2,B2:B11)

Excel Workbook
	A	B	C	D
1	Sheet Names		Crtieria
2	Jan		ID101
3	Feb		TOTAL
4	Mar		44	=SUM(Jan:Mar!D2)
5			instead of:
6			44	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A11"),C2,INDIRECT("'"&A2:A4&"'!B2:B11")))
Sum

Cell Formulas
Range	Formula
C4	=SUM(Jan:Mar!D2)
C6	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A11"),C2,INDIRECT("'"&A2:A4&"'!B2:B11")))

 

[Smitty]

I have 64 bit and so I am still sad about no MoreFunc.

Mike, I'd lose the 64-bit version and go with the 32-bit version if you can. Unless you're doing high powered computing, I haven't really seen a compelling reason to use it. To many add-ins just haven't caught up to it yet. Most everyone I've run into who tried the 64-bit version quickly switched back.

My 2 cents anyway.

 

[mgirvin]

Wow, Smitty, that is a good 2 cents worth, because I have had nothing but terrible trouble, including major operational system melt downs...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

 

[Aladin Akyurek]

Thanks, Aladin!
...

Your idea about:

'Calculating a SumIf formula per sheet, using a criteria cell from the destination sheet, combined with a regular 3d sum... Calculate locally, Access globally'

is great. I never thought of that. By that do you mean something like this:...

Yes. That is exactly what jeffreybrown has suggested.

 

[mgirvin]

I was confused. That happens often. I totally misinterpreted your post, jeffreybrown. <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>I was trying something like =SUM(--('Jan:Mar'!A2:A11=C2))...<o></o>
<o></o>
Nevertheless, jeffreybrown, your idea is great, especially since it is so easy to drill through and create the one SUMIF on each sheet! Thanks a lot!!!<o></o>
<o></o>