Using other equations inside of SUM?

[goydaeh]

I'm working on a spreadsheet for payroll, where, if an employee is
sick for seven hours, they'll enter 7S in cells B2 through O2 (two
weeks). (7V for vacation, 7H for holiday, etc.). I then want to sum
the value of these so that I'll have a total for sick time, a total
for vacation time, etc.

The equation for sick time is currently:

="Sick: "&IF(ISERR(SEARCH("S",B14)),0,LEFT(B14,1))
+IF(ISERR(SEARCH("S",C14)),0,LEFT(C14,1))+etc.

Thereby returning the number of hours sick, if they were sick, and a 0
if they weren't, for each day.

Is there a way to write this as a sum, rather than having to copy out
the IF equation 14 times?

While I'm at it, is there a way to account for someone using 10 or
more hours in a day, since LEFT is only set to pick up the first
character?

 

[Claus Busch]

Hello goydaeh,

Am Fri, 1 Jul 2011 08:35:56 -0700 (PDT) schrieb goydaeh:

="Sick: "&IF(ISERR(SEARCH("S",B14)),0,LEFT(B14,1))
+IF(ISERR(SEARCH("S",C14)),0,LEFT(C14,1))+etc.

Thereby returning the number of hours sick, if they were sick, and a 0
if they weren't, for each day.

Is there a way to write this as a sum, rather than having to copy out
the IF equation 14 times?

While I'm at it, is there a way to account for someone using 10 or
more hours in a day, since LEFT is only set to pick up the first
character?

Sick:
=SUM(IF(ISNUMBER(FIND("S",B2:O2)),LEFT(B2:O2,LEN(B2:O2)-1)*1),0)
and enter the formula with STRG+Shift+Enter (array formula)


Regards
Claus Busch