Add current total and running total

**rad mp** · 10-10-2009, 01:27 PM

In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet.
I don't know if it is possible or not and am working on a pre-existing worksheet.

Any help for this newbie Excel user is greatly appreciated.

Thanks in advance.
Michelle
schedule dummy-2.xls

**teylyn** · 10-10-2009, 05:25 PM

Hi, would you please care to point out in which cell you would want something done, and also which cells would feed into it.
The spreadsheet is not easy to understand, and you know it much better than any of us. It'd take me an hour just to make sense of what I'm seeing before I even start to understand what I'm looking for.

**rad mp** · 10-10-2009, 07:11 PM

The total for the current calendar for each employee (xray is B6 to B19) is listed the the AF:BB to 6-19 columns. I would like to set it up the the ytd column ie 6AG, 6AI, etc. to have a total of the current days scheduled and as new weeks are pasted into the C:AD columns, a grand total if you will.

**NBVC** · 10-10-2009, 10:01 PM

Firstly your dates in C3:D3, which have not been formatted to dates, are all 2008 dates and not 2009 dates....

Please fix those dates first...

Then in AG6 use formula:

=SUMPRODUCT(--($C6:$AD6="PTO"),--($C$3:$AD$3<=TODAY()))

and copy down.

You can copy this formula to other ranges, changing "PTO" to appropriate code.

**rad mp** · 10-11-2009, 12:33 PM

I'm sorry that I wasn't clear but I would like the total to the end of the schedule and when the next month is entered, then the ytd total will add the previous total and the current total together, a running total if you will.
Attached is the spreadsheet with the changes requested by the previous post.schedule dummy-2.xls

Thanks,
Michelle

**NBVC** · 10-11-2009, 07:49 PM

Maybe you can give example of expected results and how you arrived at them...

To me YTD means "total to today"

**rad mp** · 10-11-2009, 09:05 PM

I would like to be able to have a total for the current calendar (10-5/11-1). When a new schedule say, 11-2/12-1 is either pasted in or adjusted from the existing schedule, then the total for the two months will be displayed in the YTD column. Just a running total as each new schedule is made for each of the categories at the end of the calendar. "PTO", "7OR" etc.

Michelle

**NBVC** · 10-11-2009, 09:11 PM

Still not sure I get it.. but here is a guess..

In AG6,

=COUNTIF(C6:INDEX(6:6,COLUMN()-3),"PTO") copied down.

This will allow you to insert columns before Column AE and count the PTO's in addition to the one's in your first column of PTO counts...column AF

**rad mp** · 10-12-2009, 11:09 AM

Thanks for all your help.

What I was attempting to do was, have a total of of specific shifts for each employee during the month of January. Then, the next month, Feb. I want the total for Jan and Feb on February's calendar etc. I figured out how to copy existing spreadsheets and add cells from different sheets to get the results I desired.

**NBVC** · 10-12-2009, 11:44 AM

So I guess this is solved?

If so please mark your thread as Solved. If not.. please advise.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save