+ Reply to Thread
Results 1 to 2 of 2

Need help with sumproduct & dynamic ranges

  1. #1
    Registered User
    Join Date
    03-23-2004
    Posts
    23

    Need help with sumproduct & 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 are: 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.

  2. #2
    Harlan Grove
    Guest

    Re: Need help with sumproduct & dynamic ranges

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1