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)*('Shee t2: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"))


--
Regards

Roger Govier


"John D" wrote in message
...
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)*('Shee t2: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!

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.

--
Regards,
Tom Ogilvy




"John D" wrote:

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)*('Shee t2: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!

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" wrote:

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" wrote in message
...
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)*('Shee t2: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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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)*('Shee t2: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!