Thanks Arvi

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