+ Reply to Thread
Results 1 to 6 of 6

Dynamic summing of columns based on variable rows and dates

  1. #1
    Registered User
    Join Date
    08-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Dynamic summing of columns based on variable rows and dates

    How can you sum the total dollar value between two dates with dynamic rows and columns? The objective is to find the dollar value associated with a given name (row) during the week ending in the date column.

    There are two tabs: SOURCE and CALCULATION. SOURCE is a table of names as the Y axis and dates as the X axis with dollar values as the content. CALCULATION is a table of the same names in the Y axis and Week Ending dates in the X axis (i.e., where SOURCE may have 1/1, 1/2, and 1/3, CALCULATION will only show 1/7 as the end of the week).

    Some things to note:
    1. Not all Week Ending dates appear in the SOURCE table and not all dates in the SOURCE table appear in the CALCULATION table
    2. The dates (columns) are not in chronological order. (This is a copy-paste values from a pivot table that pulls from another table. I don't know why the column dates are not in order though the source data is...)
    3. Some dates appear in the future so the formula cannot be dependent on TODAY()

    A scrubbed version of the material below is here: Book1.xlsx

    Thank you!

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Dynamic summing of columns based on variable rows and dates

    Try this in C5 of the calculation tab:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    EDIT-
    drag down and across


    Note that column F (and columns P and S as well) shows 0 because there is no source data falling into that week

    Hope this helps
    Last edited by dredwolf; 11-13-2013 at 04:39 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    08-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Dynamic summing of columns based on variable rows and dates

    dredwolf--

    That worked great when I put it into my actual spreadsheet.

    However, I noticed it's sucking up a ton of processing power! (I'm using this across 100 rows and 20 columns in the Calculations tab from a source table that's 500 rows and 48 columns.) Any alternatives/ suggestions?

    Also--I'll admit I'm not 100% sure on how this is working. When I broke it down piece by piece, I'm still a little confused by it. Would you mind walking me through it? Thanks!

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Dynamic summing of columns based on variable rows and dates

    The only real alternative I can suggest is presorting the source data, then you can maybe reduce the array sizes being worked on, and/or use different functions, with an unsorted data source, I think this is about as good as I can come up with

    As to a walk through, I'm not very good at explanations, but I'll try to make it clear
    first (although not first in the formula, but it works out the same because of operator precedence) we get the rows from the source that we need by comparing the names {(SOURCE!$A$5:$A$14=$A5)}, any rows in the source column a that are equal to the name in row we are in will return a true, in the example this gives us, for row5, an array like this {True;False;False;False;False....}
    This gets multiplied against the the total array of values in the source, and because true =1 and false =0, any row that starts with the same name will be it's value, everything else will = 0
    Next, we need the columns that fall within the week so {(SOURCE!$B$4:$AZ$4>IFERROR(VALUE(B$4),0))} will compare the dates of the source information to find dates that are larger than the previous weeks last day, the iferror part is so that the first column actually has a value to work from(you may remove this from the second column and change it to this {(SOURCE!$B$4:$AZ$4>B$4)},and drag this across, which may help a bit)
    This gives us an array of source values whose dates are larger than the previous weeks end date, for the first column, something like this {True,True,True,....}, note these are separated by commas to denote columns,while the previous ones were separated by semi-colons to denote rows
    This gets multiplied against the results of the previous results to now give us values in which the row header is the same as the row header we are in, and column headers that are larger than the previous weeks end date , I won't try to write this out at all, a fair number of values, and lots of zeroes...lol
    Next we want column dates from source that are less than or equal to our column's date so{(SOURCE!$B$4:$AZ$4<=C$4)} gives us something like this, for the first column,{True,False,False,...}, which again gets mutipled by the previous results, to give us an array of values that are A> in rows that match our row header, B> dates that are largen than the last end of week date, AND C> less than or equal to the week end date of the column we are in, in the example it would look a little bit like this for the first cell{62,0,0...;0,0,0...;....}
    We wrap a Sumproduct function around it to sum it all up,(we COULD use a Sum Function, but then we would have to enter it as an array formula, and remember to CSE enter it every time we modified it, and I believe the Sumproduct to be a little faster than an array entered Sum formula..

    Hope I Didn't Make it too murky

  5. #5
    Registered User
    Join Date
    08-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Dynamic summing of columns based on variable rows and dates

    That's a great explanation. Thank you.

    I had originally been doing it with a (SUMIF([columns with dates less than Week Ending date],INDEX([Source Data],MATCH[Name Column to Name Source Column]),1):INDEX([Source Data],MATCH([Name Column to Name Source Column]),MATCH([Date Value to Date Source Row)]). ('PIVOT EXP' is my source tab and 'TRACKER' is the calculation tab.)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Essentially, by row it is summing the total value in columns with dates up to and equal to the max date and subtracting out the sum total of values in columns with dates less than the min.

    However, it wasn't working if someone listed a dollar value in the future. It would just show as error (if I removed the IFERROR). I'm not sure why though. (A workaround was to manually add $0 values with matching Week Ending dates for the weeks between the future dollar value and the current date--but that's unsustainable/unscalable.)

    Anyhow, I really appreciate your insights. Thanks!!

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Dynamic summing of columns based on variable rows and dates

    You are welcome !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Summing range of numbers according to variable dates
    By budapesh in forum Excel General
    Replies: 1
    Last Post: 05-26-2011, 05:46 PM
  2. Can't Sort rows based on Dates in 3 columns
    By bjwade62 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-29-2010, 10:48 AM
  3. [SOLVED] Summing a variable range of columns
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 06:05 AM
  4. [SOLVED] Summing a variable range of columns
    By Richard Buttrey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. Summing a variable range of columns
    By Richard Buttrey in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-28-2005, 02:06 PM

Tags for this Thread

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