Newbie Question - Weekly Schedule totals

Hello there! New to the forums but the have already been a big help. But I am stuck and am just learning the basics of Excel. I am doing a weekly work schedule (Sunday to Saturday) and I am using a dropdown list to add the appropriate shifts that I need for each day of the week. I have used a formula to give each shift in the dropdown menu a value in a different cell (ie. 0900-1730 = 8.5). I would like a totals column at the end of each week totaling the hours scheduled. Is this possible? I am using Excel 2010.

Tahnk-you

can you attach your file here please ?

Originally Posted by taccoo73

can you attach your file here please ?

Sure. Here is a s file with the portion I am looking at.sch template.xlsx
I hope this helps.

Thanks again
Cheers

My apologies. Here is the template with a before and after tab.
heers

Originally Posted by FDibbins

Hi and welcome to the forum

Hard to be specific without seeing what you are working with, but it sounds like you could use either sumif() to add up all the times, or countif() to count the number of times a shift appears, andthen * that by the hours in a shift?

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

First, the cells in your Total row are all formatted as Text - change it to General (or whatever number format you want your numbers in).

Next, here is the formula I put together:
=IF(ISNA(VLOOKUP(B4,$N$3:$O$40,2,FALSE)),0,VLOOKUP(B4,$N$3:$O$40,2,FALSE))+IF(ISNA(VLOOKUP(B5,$N$3:$O$40,2,FALSE)),0,VLOOKUP(B5,$N$3:$O$40,2,FALSE))+IF(ISNA(VLOOKUP(B6,$N$3:$O$40,2,FALSE)),0,VLOOKUP(B6,$N$3:$O$40,2,FALSE))+IF(ISNA(VLOOKUP(B7,$N$3:$O$40,2,FALSE)),0,VLOOKUP(B7,$N$3:$O$40,2,FALSE))+IF(ISNA(VLOOKUP(B8,$N$3:$O$40,2,FALSE)),0,VLOOKUP(B8,$N$3:$O$40,2,FALSE))

Put this in B9 and drag it across. It is just a repeating VLOOKUP formula, using the IF statement to change any #NA errors into 0.

Also, a good idea would be to name the range being used, like using the name TimeTable for the range $N$3:$O$40. (Formulas ribbon, Defined Names group, look for Name Manager)

Then, you could use:
=IF(ISNA(VLOOKUP(B4,TimeTable,2,FALSE)),0,VLOOKUP(B4,TimeTable,2,FALSE))+IF(ISNA(VLOOKUP(B5,TimeTable,2,FALSE)),0,VLOOKUP(B5,TimeTable,2,FALSE))+IF(ISNA(VLOOKUP(B6,TimeTable,2,FALSE)),0,VLOOKUP(B6,TimeTable,2,FALSE))+IF(ISNA(VLOOKUP(B7,TimeTable,2,FALSE)),0,VLOOKUP(B7,TimeTable,2,FALSE))+IF(ISNA(VLOOKUP(B8,TimeTable,2,FALSE)),0,VLOOKUP(B8,TimeTable,2,FALSE))

Wow, thank you. that was a great help!

Cheers