I have a table that totals absences for the last 18 months. My table includes collapsible rows and headings with a total row for 2017 and 2018. There is also a total row for each month. Because of my layout my column of days is not consistent. I've included an example below:
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Absence[/TD]
[TD]180 Day Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/2/2018[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/3/2018[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/4/2018[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/5/2018
[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February 2018[/TD]
[TD]2/1/2018[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/2/2018[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/3/2018[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/4/2018[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/5/2018[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
While totaling the month and years are easy, I also need to include a column (D) with a running 180 day total that takes into account the breaks for the total rows. I'm just not sure what equation to use. I thought maybe SUMIF but I'm not entirely sure how to execute it. Any help would be appreciated.
Would there be a way to create an equation that calculates a 180 days back from the corresponding date on the running total line, and then finds and matches any dates with absences?
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Absence[/TD]
[TD]180 Day Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/2/2018[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/3/2018[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/4/2018[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/5/2018
[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February 2018[/TD]
[TD]2/1/2018[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/2/2018[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/3/2018[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/4/2018[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2/5/2018[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
While totaling the month and years are easy, I also need to include a column (D) with a running 180 day total that takes into account the breaks for the total rows. I'm just not sure what equation to use. I thought maybe SUMIF but I'm not entirely sure how to execute it. Any help would be appreciated.
Would there be a way to create an equation that calculates a 180 days back from the corresponding date on the running total line, and then finds and matches any dates with absences?