Cumlative Total based on Rolling year not using today's date

[Tori369]

Hello All,
Please bear with me, this is my first post

I have read many threads on multiple sites (I'm only asking my question on this site though) and have yet to get an answer specific to my needs.

I work in a hospital Blood Bank/Donor Center. There are many types of donations but your limited as to how much you can donate based on date and cumlative red cell loss.

I have created a spreadsheet that calculates the culmative total, but am at a loss for how to include the rolling year. The problem is that the dates will not be successive (can be a week apart, 56 days apart, 112 days apart, etc), cannot be based on current date but only the date values keyed in. My current sheet look likes this:
<TABLE style="WIDTH: 473pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=629 border=0 x:str><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" width=81 height=25>A</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 31pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=41>B</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 33pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=44>C</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=52>D</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 90pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=120>E</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 17pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=23>F</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78>G</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 17pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=23>H</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78>I</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=89>J</TD></TR><TR style="HEIGHT: 18.75pt; mso-height-source: userset" height=25><TD class=xl36 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 61pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 37.5pt; BACKGROUND-COLOR: transparent" width=81 height=50 rowSpan=2>Date of Donation</TD><TD class=xl38 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" colSpan=3>Type of Donation</TD><TD class=xl41 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 90pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=120 rowSpan=2>Est. RBC Loss for Method Used</TD><TD class=xl43 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 17pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=23 rowSpan=2>+</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=78 rowSpan=2 x:str="Additional Loss ">Additional Loss </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 17pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=23 rowSpan=2>=</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=78 rowSpan=2 x:str="Total RBC ">Total RBC </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 67pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=89 rowSpan=2>*Cumulative Total</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 31pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" width=41 height=25>WB</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 33pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=44>SDP</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=52>dRBC</TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" width=81 height=31 x:num="39997">7/3/2009</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 31pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=41> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 33pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=44> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=52>X</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 90pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=120 x:num>540</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 17pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=23>+</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=78 x:num>25</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 17pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=23>=</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=78 x:num x:fmla="=SUM(E4,G4)">565</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=89 x:num x:fmla="=SUM(I4)">565</TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" width=81 height=31 x:num="40003">7/9/2009</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 31pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=41> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 33pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=44>X</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=52> </TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 90pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=120 x:num>25</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 17pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=23>+</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=78 x:num>25</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 17pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=23>=</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=78 x:num x:fmla="=SUM(E5,G5)">50</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=89 x:num x:fmla="=SUM(J4,I5)">615</TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" width=81 height=31 x:num="40095">10/9/2009</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 31pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=41> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 33pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=44> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=52>X</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 90pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=120 x:num>540</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 17pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=23>+</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=78 x:num>25</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 17pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=23>=</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=78 x:num x:fmla="=SUM(E6,G6)">565</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=89 x:num x:fmla="=SUM(J5,I6)">1180</TD></TR></TBODY></TABLE>
Obviously the cumlative total formula is not an issue. I'm proficient in excel, but not an expert. My formula/programming skills are average. The answer my be as plain as the nose on my face, and if so I apologise. I've been working on this sheet non stop since Monday and they would like it completed by Monday. Until yesterday I had it completed with cell formulas and calculations that displayed messages and warnings. The they threw in the rolling year based on a differnt idea then was origionally described (just add 356 to the new date, I thought that was too simple, but that's what they said at first). Any help would be greatly appreciated!

Thank you

 

[Jared_8878]

Are you looking for a sum of Column J for one year in the past to today?

if so - use a SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

 

[Andrew Poulsom]

For a rolling 12 months cumulative:

=SUMIF(A$3:A3,"<="&A3,I$3:I3)-SUMIF(A$3:A3,"<="&DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)),I$3:I3)

in K3, copied down.

 

[Tori369]

Jared_8878
Based on the dates only on the spreadsheet. So the first date starts the rolling year. Obviously though that will eventually change as dates are entered and exceed a year from the date in the first cell.


Andrew Poulsom
I saw your response when I was responding... let me see how your formula works.

Thank you both for such fast responses

 

[Tori369]

For a rolling 12 months cumulative:

=SUMIF(A$3:A3,"<="&A3,I$3:I3)-SUMIF(A$3:A3,"<="&DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)),I$3:I3)

in K3, copied down.

Thank you THANK YOU!!! Very much. This works exactly how I need it to. Now I can rework my messages and warning and should have this done by the end of today for testing.

I very much appreciate the quick response and formulas from both of you. To say I was a little stressed is an understatement, I think I can lay off the coffee and actually sleep