summing accross multiple sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been experimenting with summing accross multiple sheets using
microsoft help for the right formula but I can't get it to work.
This is what I want to do and microsoft helps says it should work this way.
=SUMPRODUCT(('Sheet2:Sheet3'!D:D=Sheet1!A3)*('Sheet2:Sheet3'!E:E="Y"))
However this is what results every time.
=SUMPRODUCT(('Sheet2:[Sheet3]Sheet3'!D:D=Sheet1!A3)*('Sheet2:[Sheet3]Sheet3'!E:E="Y"))
Can anyone help me please!
 
Hi John

Unless you are using XL2007, you cannot pass whole columns as a
parameter to Sumproduct.
Also, I do not think you can sum across sheets in that way

Try
=SUMPRODUCT((Sheet2!D1:D1000=Sheet1!A3)*(Sheet2!E1:E1000="Y"))+
SUMPRODUCT((Sheet3!D1:D1000=Sheet1!A3)*(Sheet3!E1:E1000="Y"))
 
Arrayformulas (which is what you are trying to achieve with sumproduct) don't
support 3D references as you show.
Unless you are using xl2007, arrayformulas and sumproduct use as an array
formula do not work with an entire column.
 
Thank you Roger,
Its nice to know that I have finally surpassed Excel's abilities in
something. I see I shall need to get 2007.

Roger Govier said:
Hi John

Unless you are using XL2007, you cannot pass whole columns as a
parameter to Sumproduct.
Also, I do not think you can sum across sheets in that way

Try
=SUMPRODUCT((Sheet2!D1:D1000=Sheet1!A3)*(Sheet2!E1:E1000="Y"))+
SUMPRODUCT((Sheet3!D1:D1000=Sheet1!A3)*(Sheet3!E1:E1000="Y"))


--
Regards

Roger Govier


John D said:
I have been experimenting with summing accross multiple sheets using
microsoft help for the right formula but I can't get it to work.
This is what I want to do and microsoft helps says it should work this
way.
=SUMPRODUCT(('Sheet2:Sheet3'!D:D=Sheet1!A3)*('Sheet2:Sheet3'!E:E="Y"))
However this is what results every time.
=SUMPRODUCT(('Sheet2:[Sheet3]Sheet3'!D:D=Sheet1!A3)*('Sheet2:[Sheet3]Sheet3'!E:E="Y"))
Can anyone help me please!
Click to expand...
Click to expand...
 
Hi John

You can use a range of D1:D65535, only 1 row short of a full column, so
not really exceeding XL2003's capabilities.
XL2007 won't allow you to use Sumproduct across sheets either, so don't
upgrade for that reason.

Having said that, despite a few speed issues and charting (which I don't
use a great deal), I think XL2007 is great.

--
Regards

Roger Govier


John D said:
Thank you Roger,
Its nice to know that I have finally surpassed Excel's abilities in
something. I see I shall need to get 2007.

Roger Govier said:
Hi John

Unless you are using XL2007, you cannot pass whole columns as a
parameter to Sumproduct.
Also, I do not think you can sum across sheets in that way

Try
=SUMPRODUCT((Sheet2!D1:D1000=Sheet1!A3)*(Sheet2!E1:E1000="Y"))+
SUMPRODUCT((Sheet3!D1:D1000=Sheet1!A3)*(Sheet3!E1:E1000="Y"))


--
Regards

Roger Govier


John D said:
I have been experimenting with summing across multiple sheets using
Microsoft help for the right formula but I can't get it to work.
This is what I want to do and microsoft helps says it should work
this
way.
=SUMPRODUCT(('Sheet2:Sheet3'!D:D=Sheet1!A3)*('Sheet2:Sheet3'!E:E="Y"))
However this is what results every time.
=SUMPRODUCT(('Sheet2:[Sheet3]Sheet3'!D:D=Sheet1!A3)*('Sheet2:[Sheet3]Sheet3'!E:E="Y"))
Can anyone help me please!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top