Hi

Ranges aren't of same dimension!
When you used dynamic ranges (defined through OFFSET), then in COUNTIF part
use same column for all them. I.e.
StoreNo = OFFSET('Sales Mix'!$B$2,,,COUNTIF('Sales Mix'!$B:$B,"<")-1,1)
Site = OFFSET('Sales Mix'!$C$2,,,COUNTIF('Sales Mix'!$B:$B,"<")-1,1)
etc.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"John" wrote in message
...
Thanks Arvi, I picked the second defined Range name that you suggested and
now getting a #Value! error


"Arvi Laanemets" wrote in message
...
Hi

You can't use references to whole column with SUMPRODUCT

I.e define your ranges as
StoreNo ='Sales Mix'!$A$2:$A$65536
or like this
StoreNo =OFFSET('Sales Mix'!$A$2,,,COUNTIF('Sales Mix'!$A:$A,"<")-1,1)
(I assume you have headers in row 1, and that data range in column A is
continuous)


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


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