Forum Discussion
Need help with formula
Let me apologize in advance if i have a hard time explaining this. I'm not even sure if excel can do this. I have a spreadsheet where i want to be able to sum up Overtime Hours and Comp hours in the same row. What i would like to do isL: for overtime put in 2O or comp time put in 2C and have excel look at the O or C, pull the number before those and sum them up in the appropriate column. I tried the CountIF statement and can get it to count the cells if i put an O or C in them, but it ignores the cell once i put a number in it.. Any Suggestions?
Does anyone know how to adjust the formula to see the previous year's calendar view for each individual employee? When we rolled into January, I can no longer pull up the calendar view for 2017... HELP!?
I'm asking in relation to the employee attendance tracker preformatted excel sheet
in 1stQ
comp
=SUMPRODUCT(VALUE(LEFT($H6:$CS6,LEN($H6:$CS6)-1)) * (RIGHT($H6:$CS6,1)="c"))
overtime
=SUMPRODUCT(VALUE(LEFT($H6:$CS6,LEN($H6:$CS6)-1)) * (RIGHT($H6:$CS6,1)="o"))
I am trying your solution.. attempted to type it myself and when that didn't work i just copied and pasted your formula into the cell and i get #VALUE! in the cell....
I made mistakes. I assumed the pattern of every cell in $H6:$CS6 like number + type.
For comp (updated formula)
=SUMPRODUCT(IFERROR(VALUE(LEFT($H6:$CS6,LEN($H6:$CS6)-1)),0) * (IFERROR(RIGHT($H6:$CS6,1),0)="c"))
after typing the above formula, keep in editing formula mode, press CTRL + SHIFT + ENTER to make it as a array formula. Then, the formula will look like
{=SUMPRODUCT(IFERROR(VALUE(LEFT($H6:$CS6,LEN($H6:$CS6)-1)),0) * (IFERROR(RIGHT($H6:$CS6,1),0)="c"))}For overtime (updated formula)
=SUMPRODUCT(IFERROR(VALUE(LEFT($H6:$CS6,LEN($H6:$CS6)-1)),0) * (IFERROR(RIGHT($H6:$CS6,1),0)="o"))
remember to turn it to array formula.
