Need help with sumproduct & dynamic ranges

[bill_s1416]

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

On the summary sheet where I will place this this sumproduct formula i
cell B10. I have in cells B1:B4 the values I am looking for that I ca
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 sumproduc
formula to sum is variable.

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

 

[Harlan Grove]

bill_s1416 wrote...
....

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.

....

=SUMPRODUCT((Data!$A$29:$A$65536=$B$1)*(Data!$B$29:$B$65536=$B$2)
*(Data!$C$29:$C$65536=$B$3)*Data!$D$29:INDEX(Data!$D$65536:$AA$65536,$B$4))

where Data is a placeholder for the actual name of the worksheet
containing your sales data. Reduce the 65536 instances to the actual
extent of your data in terms of rows. Smaller ranges will recalc
quicker.