|
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 |
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 |
| All times are GMT +1. The time now is 04:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com