Add a Dynamic Range with 2 Conditions Q

[John]

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

 

[Frank Kabel]

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

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

 

[John]

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?

 

[Arvi Laanemets]

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)

 

[John]

Thanks Arvi, I picked the second defined Range name that you suggested and
now getting a #Value! error

 

[Arvi Laanemets]

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.

 

[John]

Thanks Arvi

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<At>tarkon.ee)




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

 

[Frank Kabel]

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