Results 1 to 6 of 6

SUMPRODUCT Dynamic Help

Threaded View

  1. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: SUMPRODUCT Dynamic Help

    I modified the date structure on the SPT FCST and SPT worksheets and made the same modification to the date row referenced on the Overview 2017 worksheet. I made real dates instead of combining month names with years.
    The formula that I came up with is an INDEX/MATCH/MATCH formula using concatenated values to be looked up against concatenated columns. This is an array formula so enter with Ctrl + Shift + Enter.
    Formula: copy to clipboard
    =IF($E$5="Sales",IFERROR(INDEX(SPT!$E$3:$AC$100,MATCH($A43&$B43&$C43&$D43,SPT!$A$3:$A$100&SPT!$B$3:$B$100&SPT!$C$3:$C$100&SPT!$D$3:$D$100,0),MATCH('Overview 2017'!E$7,SPT!$E$2:$AC$2,0)),""),IFERROR(INDEX('SPT FCST'!$E$4:$R$100,MATCH($A43&$B43&$C43&$D43,'SPT FCST'!$A$4:$A$100&'SPT FCST'!$B$4:$B$100&'SPT FCST'!$C$4:$C$100&'SPT FCST'!$D$4:$D$100,0),MATCH('Overview 2017'!E$7,'SPT FCST'!E$3:R$3,0)),""))
    Attached Files Attached Files
    Last edited by newdoverman; 01-11-2017 at 09:00 PM. Reason: Wrong file uploaded
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumproduct with a dynamic range...is that the only way?
    By som3on3_10 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2014, 02:35 AM
  2. [SOLVED] SUMPRODUCT Formula with Dynamic Last Row
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2013, 10:43 AM
  3. Dynamic Sumproduct Lookup
    By matt4003 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-30-2010, 03:14 AM
  4. Sumproduct for dynamic range
    By Bob@Sun in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2010, 06:32 AM
  5. Sumproduct with dynamic array
    By benaw in forum Excel General
    Replies: 3
    Last Post: 10-18-2009, 07:24 PM
  6. Need help with sumproduct & dynamic ranges
    By bill_s1416 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2006, 11:40 PM
  7. [SOLVED] Need help with sumproduct and dynamic ranges
    By Bill_S in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-18-2006, 09:25 PM

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