summing rows with different criterias...

[shpon]

Hi,
I know this must be a very simple task, but I can't figure
it out.
what i want to do is the following:
I have a row that has different values in it. for example,
cell1 cell2 cell3 cell4 cell5 cell6 cell7 cell8 cell9
8 8v 5sl 8 7sl 8 ? ? ?

at cell 7, (I want total of sl)
at cell 8, (I want total of v)
at cell 9, (here I want total of regular hours)

in cells that i only have number, it is for regular hours,
in cells where I have number followed by v, it is for
vacation hours, and in cellls where i have number followed
by sl, it is for sick leave hours.

What i want to do is a formula that would analyze cells
1-6, and in cell 7 add the total only for cells that have
sl, and the same thing for cell 8, adding v, and so on...

I tried the SUMIF, but it did not work...i tried to custom
formate the cells for number and text, and that did not
work either...anyone has any suggestion?
I would greatly appreciate it.

 

[Dave Peterson]

are all the numbers single digits?

If yes, then this worked ok for me:

(I used columns A:E for my input range)

For SL:
=SUM(IF(RIGHT(A1:E1,2)="SL",--LEFT(A1:E1,1)))

For V:
=SUM(IF(RIGHT(A1:E1,1)="V",--LEFT(A1:E1,1)))

But both of these formulas are array formulas. Hit ctrl-shift-enter instead of
enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

for regular hours:
=sum(a1:e1)
(excel will ignore the text values)

 

[shpon]

Thank you so much...it was awesome....
what if in the last cell i want total of
everything...meaning i want to add up the regular hours,
and v hours and sl hours? is that possible?
thanks a bunch

 

[Dave Peterson]

Let the other formulas do the dirty work and just add them up:

=sum(g1:h1)

(Modify that range if I missed!)