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