SUMIF formula stopped working in timesheet

The formula that I entered in column "AE" of my timesheet has stopped calculating the total number of "Worked Hours" an employee worked under the "Program Code" selected during a pay period.

It was the first formulas that I entered, and it worked perfectly. However, since entering the additional formulas in other columns of the timesheet it no longer calculates, and I've spent days trying to figure out why.

Formula in column AE (I used this formulas for each "Program Code," changing only the "X" row value in "*"&ADX$"*": =SUMIF($C$12:$Y$12,"*"&AD3&"*",$E$11:$Z$11)+SUMIF($C$14:$Y$14,"*"&AD3&"*",$E$13:$Z$13)+SUMIF($C$16:$Y$16,"*"&AD3&"*",$E$15:$Z$15)+SUMIF($C$18:$Y$18,"*"&AD3&"*",$E$17:$Z$17)+SUMIF($C$20:$Y$20,"*"&AD3&"*",$E$19:$Z$19)+SUMIF($C$22:$Y$22,"*"&AD3&"*",$E$21:$Z$21)+SUMIF($C$24:$Y$24,"*"&AD3&"*",$E$23:$Z$23)+SUMIF($C$26:$Y$26,"*"&AD3&"*",$E$25:$Z$25)+SUMIF($C$28:$Y$28,"*"&AD3&"*",$E$27:$Z$27)+SUMIF($C$30:$Y$30,"*"&AD3&"*",$E$29:$Z$29)+SUMIF($C$32:$Y$32,"*"&AD3&"*",$E$31:$Z$31)+SUMIF($C$34:$Y$34,"*"&AD3&"*",$E$33:$Z$33)+SUMIF($C$36:$Y$36,"*"&AD3&"*",$E$35:$Z$35)+SUMIF($C$38:$Y$38,"*"&AD3&"*",$E$37:$Z$37)


Link to Google Sheet: Forum wouldn't allow because I'm new.

Thanks for the reply, FDibbins.

You say it has "stopped working", what exactly does that mean?
The formulas returns the value "0.00" and no error message.

What is in between the summing rows?
It's a lot to try to explain. I snipped an image of the timesheet, but couldn't attach it. I have a link to my timesheet. Is there any way that I can send it to you?


Would this work: =SUMIF($C$12:$Y$38,"*"&AD3&"*",$E$11:$Z$37)?
I tried it an received the same result, "0.00".

Also, I checked the cell format and it is the same as the cells with formulas to calculate fringe benefit hours, "number". Odd thing is that I use the same formula in these cells, just different reference columns...?

Originally Posted by FDibbins

Agreed, I get the same instruction as well

Every option to copy to my personal Gmail account would've required me to re-enter all of my formulas, formatting, and validations. However, I've since confirmed your shared access.

Originally Posted by 6StringJazzer

obedb4sac, this is not a public document. When I click it on it, I must request permission from you to see it.

Every option to copy to my personal Gmail account would've required me to re-enter all of my formulas, formatting, and validations. However, I've since confirmed your shared access.

Originally Posted by 6StringJazzer

The layout of your data is working against you. There is a sacrifice of functionality for appearance.

I downloaded your file so I could work on it locally in Excel. I did not want to edit it in Google and mess up your original.

The best solution would be to reorganize your sheet:

• You have data for many entries each spread onto two rows. The best practice is to have one entry per row--that is, one day, on program code.
• Merged cells are almost always a bad idea.
• The day is in two cells (columns A:B), but it should be one day value with desired formatting in a single cell.
• Columns AK:AM should be on a different sheet since their row setup is so different than the other data.

I am developing a specific suggestion but not sure if I'll have time; it's not complex but it is time-consuming to do this kind of reorganization.

Thanks! I'll apply your previous suggestions and follow-up.

I cannot find that formula anywhere, can you tell me where it is?[/QUOTE]

If looking at current version of shared doc: AG
If looking at previously downloaded: AE

Originally Posted by 6StringJazzer

See Timesheet Copy and Code Distribution tabs for redesign. It makes all the formulas a lot easier. However, I don't know what your data entry needs are.

THANK YOU! THANK YOU! THANK YOU! I doubt that I would've ever thought of this layout.

Will do. I plan to finish working on the timesheet tomorrow.