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

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

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

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

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

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

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

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