Need help with sumproduct and dynamic ranges
 

I need help creating a sumproduct formula to sum the sales data for a
particular sales territory. The data sheet that the formula will reference
is set up such that the column headings are in row 28 and the data begins in
row 29 (I won't bore you with why) and continues on for thousands of rows.
The columns a Brand, Territory, Product, Sales1 (sales for current month),
Sales 2 (sales for last month), Sales 3,....,Sales 24.

On the summary sheet where I will place this this sumproduct formula in cell
B10. I have in cells B1:B4 the values I am looking for that I can change and
the formula in cell B10 should then update:
B1 = Brand in question
B2 = Territory in question
B3 = Product in question
B4 = # of sales months to sum.

B4 is a wrinkle because the # of columns of data for the sumproduct formula
to sum is variable.

I am guessing that some combination of dynamic ranges and a sumproduct
formula maybe could accomplish this. How can I make this happen? Any advice
would be greatly appreciated.

Need help with sumproduct and dynamic ranges
 

you can create a sumproduct formula like the following

=SUMPRODUCT((C8:C10="bob")*(D8:OFFSET(D10,0,$C$14-1,1,1)))

where in my case cell c14 would contain the # of columns of data starting in
column d to sum using the function. Note I did the offset off of cell d10,
as i assumed I (you) know the last row of the database.

"Bill_S" wrote:

I need help creating a sumproduct formula to sum the sales data for a
particular sales territory. The data sheet that the formula will reference
is set up such that the column headings are in row 28 and the data begins in
row 29 (I won't bore you with why) and continues on for thousands of rows.
The columns a Brand, Territory, Product, Sales1 (sales for current month),
Sales 2 (sales for last month), Sales 3,....,Sales 24.

On the summary sheet where I will place this this sumproduct formula in cell
B10. I have in cells B1:B4 the values I am looking for that I can change and
the formula in cell B10 should then update:
B1 = Brand in question
B2 = Territory in question
B3 = Product in question
B4 = # of sales months to sum.

B4 is a wrinkle because the # of columns of data for the sumproduct formula
to sum is variable.

I am guessing that some combination of dynamic ranges and a sumproduct
formula maybe could accomplish this. How can I make this happen? Any advice
would be greatly appreciated.

Need help with sumproduct and dynamic ranges
 

Select the whole table from row 29 in the Brand column to whatever the last
row can be in the 24th Sales column and give it a name like MyTable
(insertnamedefine), then you can use this formula

=SUMPRODUCT((INDEX(MyTable,,1)=B1)*(INDEX(MyTable, ,2)=B2)*(INDEX(MyTable,,3)=B3)*(INDEX(MyTable,,4): INDEX(MyTable,,3+B4)))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Bill_S" wrote in message
...
I need help creating a sumproduct formula to sum the sales data for a
particular sales territory. The data sheet that the formula will
reference
is set up such that the column headings are in row 28 and the data begins
in
row 29 (I won't bore you with why) and continues on for thousands of rows.
The columns a Brand, Territory, Product, Sales1 (sales for current
month),
Sales 2 (sales for last month), Sales 3,....,Sales 24.

On the summary sheet where I will place this this sumproduct formula in
cell
B10. I have in cells B1:B4 the values I am looking for that I can change
and
the formula in cell B10 should then update:
B1 = Brand in question
B2 = Territory in question
B3 = Product in question
B4 = # of sales months to sum.

B4 is a wrinkle because the # of columns of data for the sumproduct
formula
to sum is variable.

I am guessing that some combination of dynamic ranges and a sumproduct
formula maybe could accomplish this. How can I make this happen? Any
advice
would be greatly appreciated.