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.

=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>

 

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