# Dynamic summing of columns based on variable rows and dates

1. ## 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. ## Re: Dynamic summing of columns based on variable rows and dates

Try this in C5 of the calculation tab:
Formula:
`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

3. ## 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. ## 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. ## 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:
`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. ## Re: Dynamic summing of columns based on variable rows and dates

You are welcome !

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