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]
[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]