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