Hi
you can't use ranges such as A:A within SUMPRODUCT. change the names to
StoreNo ='Sales Mix'!$A1:$A1000
or somethign similar

--
Regards
Frank Kabel
Frankfurt, Germany

John wrote:
Thanks Frank, little did I think one could write so much on
SUMPRODUCT!

I re-hashed your formula to as follows but am receiving a #Num! error

=SUMPRODUCT(--(StoreNo=3047),--(Item="43"),Sales_Units)

The ranges above, which have the same row numbers are constructed as
follows eg. StoreNo ='Sales Mix'!$A:$A

Are Range Names allowed in SUMPRODUCT formulas?



"Frank Kabel" wrote in message
...
Hi

=SUMPRODUCT(--('data'A1:A100="London"),--('data'!C1:C100=43),'data'!E1:E100)

See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

John wrote:
I have a Range area which is dynamic and I wish to create a formula
that will add up values within this dynamic range if two conditions
exist

Col A = Location
Col C = Product Number
Col E = Sales Units (of the products in Col C that have been sold in
Col A - location)

I only wish to add some of the products that are listed, not all.
Therefore is cell A1 on Sheet1 I want to create a formula that will
do the following

Add up all quantities that are in Col E that relate to Product
Number "43", that are sold at Location - "London"

How can I do that?

Thanks