Sumif - Multiple Sheets

[cafeaulait]

Hi all

How can I sumif over multiple tabs?? Without using sumif(a:a,sheet1!a1,c:c)+sumif(a:a,sheet2!a1,c:c)

Like sumif(sheet1:sheet2!a:a,a1,sheet1:sheet2!c:c) I do not want to use a macro. I have about 80 sheets.

Sheet1
Apples 93
Pears 49
Oranges 20
Bananas 35

Sheet2
Apples 100
Pears 13
Oranges 2
Bananas 350

Many thanks

 

[jeffreybrown]

See if this helps

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

 

[cafeaulait]

Many thanks, I will try this tomorrow morning at work!!

 

[jeffreybrown]

Wishing you all the best, let us know if you need anymore help

 

[cafeaulait]

Re: Sumif - Multiple Sheets - URGENT

The formula =SUMPRODUCT(SUMIF(INDIRECT("'"&"Tabs"&"'!A:A"),$B$348,INDIRECT("'"&"Tabs"&"'!S:S"))) is not working, it returns #REF

Tabs range is defined as =OFFSET('Tab List'!$A$1,0,0,COUNTA('Tab List'!$A:$A),1)

Can I reference entire columns in SUMPRODUCT???

 

[jeffreybrown]

It looks like you have some extra "" in the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&"Tabs"&"'!A:A"),$B$348,INDIRECT("'"&"Tabs"&"'!S:S")))

and in 2007/2010 you can reference entire columns within sumproduct, but it probably is not a good practice.

You could either reference the ranges larger than you expect to have or you could setup a couple of named ranges an then use them in place of the ranges.

Just need to make sure those ranges will always be the same if using named ranges. Example: column A is only filled down to row 456 and column C is filled to row 389.

http://xldynamic.com/source/xld.LastValue.html

 

[cafeaulait]

Thank you I took out the quotation marks and it now works!!!

 

[jeffreybrown]

You're very welcome...glad you got it all sorted out and thanks for the feedback

 

[aggiesmurf]

See if this helps

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

This really helped me out too!! Thanks!!!!