| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Add a Dynamic Range with 2 Conditions Q
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
| Dynamic Range Problem | Excel Discussion (Misc queries) | |||
| How do I sum a range after 2 different conditions are met (2 colu. | Excel Discussion (Misc queries) | |||
| named range refers to: in a chart | Excel Discussion (Misc queries) | |||
| range names | Excel Worksheet Functions | |||
Hybrid Mode
