I have a table of numbers that represent hours worked each day by several employees. Each cell is one day for one employee. Some cells include an "*" character denoting that those hours do not contribute to the departmental total. When calculating the departmental total, I simply do a sum of all the cells and calc ignores those values that include the asterisks. However, when I calculate the sum of the hours for the individual employee, I want to include those hours.
After doing some experimentation, I've arrived at the following clunky formula:
=SUM(VALUE(SUBSTITUTE(C22,"*","")),VALUE(SUBSTITUTE(D22,"*","")),VALUE(SUBSTITUTE(E22,"*","")),VALUE(SUBSTITUTE(F22,"*","")),VALUE(SUBSTITUTE(G22,"*","")),VALUE(SUBSTITUTE(H22,"*","")),VALUE(SUBSTITUTE(I22,"*","")))
While this works, it's terrible! Is there a way to iterate a set of functions over a set of cells? Something like:
=SUM(ITERATE_FUNCTION(VALUE(SUBSTITUTE(X,"*","")),C22:I22))
Or is there an easier way to accomplish my goal?
Thanks!
For Calc, there is one super-power function for working with multiple values: SUMPRODUCT. With a little creativity, it can do just about anything.
=SUMPRODUCT(VALUE(SUBSTITUTE(C22:I22;"*";"")))
It acts as an array function but Ctrl+Shift+Enter is not needed, making things much easier.