Sum table data with conditions and date in headers

[jbellows]

I am trying to figure out a way to solve an equation for summing data within a table based on date conditions (with date headers that have been converted to text by excel when VBA was refreshing the query) without having to use an array (I have a solution with DATEVALUE and an Array, see example.

[TABLE="width: 977"]
<tbody>[TR]
[TD]=DATEVALUE(B2)[/TD]
[TD="align: right"]4/30/2016[/TD]
[TD="align: right"]5/31/2016[/TD]
[TD="align: right"]6/30/2016[/TD]
[TD="align: right"]7/31/2016[/TD]
[TD="align: right"]8/31/2016[/TD]
[TD="align: right"]9/30/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Label[/TD]
[TD]4/30/2016[/TD]
[TD]5/31/2016[/TD]
[TD]6/30/2016[/TD]
[TD]7/31/2016[/TD]
[TD]8/31/2016[/TD]
[TD]9/30/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100000 · Revenue[/TD]
[TD="align: right"]5000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]1500000[/TD]
[TD="align: right"]8000000[/TD]
[TD="align: right"]10000000[/TD]
[TD="align: right"]7500000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]130000 · Interest Income[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]140000 · Misc. Income[/TD]
[TD="align: right"]4820[/TD]
[TD="align: right"]13496[/TD]
[TD="align: right"]12660.72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21578.59[/TD]
[TD="align: right"]9467.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]150000 · Expense Reimbursement.[/TD]
[TD="align: right"]121502.89[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]71320.52[/TD]
[TD="align: right"]44920.35[/TD]
[TD="align: right"]18303.51[/TD]
[TD="align: right"]15560.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[TD="align: right"]5136322.89[/TD]
[TD="align: right"]2019496[/TD]
[TD="align: right"]1588981.24[/TD]
[TD="align: right"]8048920.35[/TD]
[TD="align: right"]10041882.1[/TD]
[TD="align: right"]7526028.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This will be on another sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD="align: right"]4/30/2016[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Date[/TD]
[TD="align: right"]6/30/2016[/TD]
[TD="align: right"]9/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100000 · Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current solution[/TD]
[TD="align: right"]8500000[/TD]
[TD="colspan: 9"]=SUM(IF($A2:$A13=$A17,IF(($A$1:$G$1<=B16)*($A$2:$G$2>=DATE(YEAR(B15),1,1)),$A2:$G13, 0), 0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[crazydragon84]

not sure what exactly you need. do you just need a sum of row value that is between the start and end date? you can use a combination of match, indirect (?), and sumifs, no?

=sumifs(match(A17,A2:13,0),$1:$1,">=" & Start_Date, $1:$1,"<=" & End_Date)

something like that. You might need to change match(A17,A2:13,0) to indirect(match(A17,A2:13,0)&":"&match(A17,A2:13,0)) to select the row number for sumifs

 

[jbellows]

Trying to be able to do this within a table reference structure if possible, as this table is refreshed with a query and changes is size regularly. Also, the dates are text in the headers, and not values. When the query is run in VBA and refreshes the table, it is converted to text when it is used as a Header of the table.

 

[crazydragon84]

yes i understand, so everything under row 2 is table and your date value is a calc based of header row. your inputs for sum in the current selection is start date, end date, and 10000 Revenue (which you are looking up against column A of that table).

why can't you use my formula? I missed that part that you said its on a separate sheet so you'll need to add sheet reference in front of all the ranges.

 

[jbellows]

I am trying to be able to get rid of the DATEVALUE and the array formulas, and to be able to figure out how to achieve the formula using table references (i.e. sumifs(Table,Table[label],A17,Table[Headers#],">="&StartDate,Table[Headers#],"<="&EndDate)). I cannot figure out how to get a formula to see and use the table headers as formulas.