How to use array formula for three variables?
 

I am trying to create a spreadsheet to track vacation, sick and holiday time
for 30 employees. Can anyone help me with the formulas? I want to have the
three totals summed up in one column at the end, is this possible? I will
enter any times like this: v3,s3,h0 in each daily column. Thanks =)

Melissa,

I think this is what you mean

=SUM(IF(NOT(ISERROR((LOWER(LEFT(A20:A29,1))="v")*( SUBSTITUTE(LOWER(A20:A29),
"v","")))),--(SUBSTITUTE(LOWER(A20:A29),"v",""))))

it is an array formula so commit with Ctrl-SHift-Enter. Add more for s and
h.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MelissaS" wrote in message
...
I am trying to create a spreadsheet to track vacation, sick and holiday
time
for 30 employees. Can anyone help me with the formulas? I want to have
the
three totals summed up in one column at the end, is this possible? I will
enter any times like this: v3,s3,h0 in each daily column. Thanks =)

With your data in Cell C2 and downward - I set up 3 new columns with D1 = v
,E1 = s , and F1 = h
and then entered in D2:
=IF(LEFT($C2,1)="v",VALUE(RIGHT($C2,LEN($C2)-1)),0)
copied right changing "v" to "s" and "h" appropriately
then copy each row 3 DEF down
a sum() of D E and F gets you total v, s and h;
HTH




"Bob Phillips" wrote in message
...
Melissa,

I think this is what you mean


=SUM(IF(NOT(ISERROR((LOWER(LEFT(A20:A29,1))="v")*( SUBSTITUTE(LOWER(A20:A29),
"v","")))),--(SUBSTITUTE(LOWER(A20:A29),"v",""))))

it is an array formula so commit with Ctrl-SHift-Enter. Add more for s and
h.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MelissaS" wrote in message
...
I am trying to create a spreadsheet to track vacation, sick and holiday
time
for 30 employees. Can anyone help me with the formulas? I want to have
the
three totals summed up in one column at the end, is this possible? I
will
enter any times like this: v3,s3,h0 in each daily column. Thanks =)