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!