# SUMIF across sheets without INDIRECT

#### mgirvin

##### Well-known Member
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
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
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
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?

##### MrExcel MVP

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:

#### mgirvin

##### Well-known Member

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

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

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

##### MrExcel MVP
...

'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
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>

Replies
40
Views
816
Replies
6
Views
64
Replies
29
Views
529
Replies
3
Views
383
Replies
3
Views
587

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.

### Which adblocker are you using?

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

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