SUMIF across sheets without INDIRECT

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
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
AB
1IDUnits
2ID1023
3ID1003
4ID1021
5ID1024
6ID1015
7ID10210
8ID1005
9ID1008
10ID1013
11ID1011
Jan




Excel Workbook
AB
1IDUnits
2ID1018
3ID1007
4ID1024
5ID1018
6ID1026
7ID10110
8ID1014
9ID10210
10ID10210
11ID1022
Feb




Excel Workbook
AB
1IDUnits
2ID1013
3ID1027
4ID1025
5ID1026
6ID1007
7ID1006
8ID1001
9ID1001
10ID1012
11ID1025
Mar




Excel Workbook
ABC
1Sheet NamesCrtieria
2JanID101
3FebTOTAL
4Mar44
Sum
Cell Formulas
RangeFormula
C4=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A11"),C2,INDIRECT("'"&A2:A4&"'!B2:B11")))
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,147
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

Well-known Member
Joined
Feb 15, 2002
Messages
5,022
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
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
ABCD
1IDUnitsSUM with Criteria from Sheet 'SUM'
2ID10239
3ID1003
4ID1021
5ID1024
6ID1015
7ID10210
8ID1005
9ID1008
10ID1013
11ID1011
Jan
Cell Formulas
RangeFormula
D2=SUMIF(A2:A11,Sum!C2,B2:B11)



Excel Workbook
ABCD
1IDUnitsSUM with Criteria from Sheet 'SUM'
2ID101830
3ID1007
4ID1024
5ID1018
6ID1026
7ID10110
8ID1014
9ID10210
10ID10210
11ID1022
Feb
Cell Formulas
RangeFormula
D2=SUMIF(A2:A11,Sum!C2,B2:B11)



Excel Workbook
ABCD
1IDUnitsSUM with Criteria from Sheet 'SUM'
2ID10135
3ID1027
4ID1025
5ID1026
6ID1007
7ID1006
8ID1001
9ID1001
10ID1012
11ID1025
Mar
Cell Formulas
RangeFormula
D2=SUMIF(A2:A11,Sum!C2,B2:B11)



Excel Workbook
ABCD
1Sheet NamesCrtieria
2JanID101
3FebTOTAL
4Mar44=SUM(Jan:Mar!D2)
5instead of:
644=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A11"),C2,INDIRECT("'"&A2:A4&"'!B2:B11")))
Sum
Cell Formulas
RangeFormula
C4=SUM(Jan:Mar!D2)
C6=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A11"),C2,INDIRECT("'"&A2:A4&"'!B2:B11")))
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
I was confused. That happens often. I totally misinterpreted your post, jeffreybrown. :confused: <?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" o:preferrelative="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><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>I was trying something like =SUM(--('Jan:Mar'!A2:A11=C2))...<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
 

Forum statistics

Threads
1,147,734
Messages
5,742,865
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top