Counting numbers (if) certains cells have txt

**Hondoshaver** · 04-02-2010, 12:09 PM

Hello all, I am not very seasond in advanced excel so this may seem rather trivial to most of you but here goes...

I am trying to setup a time sheet that will count the hours worked in a week and log that as "Regular time" and then if somone takes a vacation day they log it into the time sheet as, say, "8 V". At first I couldn't get the =SUM function to add these because of the text, but now I just made them 2 small seperate cells where they can imput 8(hours) and V(if they took vacation in the other box). Now yay it SUMs again properly, but I need to be able to distinguish between regular hours (just an 8, 12, or however many hours worked) and Vacation, personal time, or sick time.

I don't have to make it count up each individually just IF there is a letter in box X2 then count X1 in the SUM if not omit it and go on to the next day.

So say I take a personal day on monday, a sick day on tuesday and worked normally the rest of the week, at the total it will say 40 (good) and at the "Amount of regular hours" box it will also say 40 (bad) when I need to be able to set it up to not count the 8 hours of personal time and the 8 hours of sick time taken. So the regular total should be 24.

I can have everyone just put in 24 ,but I was just wondering if I can set it up to do that for them.

Thanks in advance for any help and don't hesitate to tell me to just have them do the math in their head and put it in the box, if it would be beyond my know-how to do it haha.

-

Hondo

**DonkeyOte** · 04-02-2010, 12:30 PM

I would suggest posting a sample file - but in essence you can apply a SUMIF across the entire range.

For sake of example assume:

1. hours in A1,C1,E1,G1,I1 with "type" in B1,D1,F1,H1,J1
2. regular type is denoted by "R" in B,D etc...

Sum of Regular hours can be determined with

=SUMIF(B1:J1,"R",A1:I1)

If you wanted to sum say Vacation (V) and Sick (S) you could use:

=SUM(SUMIF(B1:J1,{"V","S"},A1:I1))

**Marcol** · 04-02-2010, 12:30 PM

Something on these lines?

=SUMIF($B$1:$B$12,"",$A$1:$A$12) (Regular)

=SUMIF($B$1:$B$12,"V",$A$1:$A$12) (Vacation)

etc.......

Deal with the good / Bad with Conditional formatting.

**Hondoshaver** · 04-02-2010, 05:16 PM

Thanks a ton

Thats what I needed you 2 are awesome!

-Hondo

**Marcol** · 04-02-2010, 05:48 PM

Thanks Hondo,

But only one is awesome,DonkeyOte, I'm just trying to help!

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.