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...
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.