Sum table data with conditions and date in headers

jbellows

New Member
Joined
Apr 23, 2018
Messages
3
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]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top