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

Tori369

New Member
Joined
Jul 28, 2010
Messages
3
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! :biggrin:

Thank you
 

Jared_8878

Board Regular
Joined
Apr 7, 2010
Messages
161
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…)
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 28, 2010
Messages
3
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 :biggrin:
 

Tori369

New Member
Joined
Jul 28, 2010
Messages
3
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 :biggrin:
 

Forum statistics

Threads
1,082,145
Messages
5,363,384
Members
400,734
Latest member
sanpr

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top