Formula tweek

[Funtreats]

I have searched and studied the sumif and sumifs and still cant find an answer.

I would like help writing a formula to only total cells that produce a negative number. Example Tuesday 7.5<8 = .5

To calculate how much vacation time an employee has used.

Monday 8 hours
Tuesday 7.5 hours
Wednesday 8 hours
Thursday 8.5 hours
Friday 7 hours

In this case the total hours used .5 on Tuesday and 1.0 on Friday would be 1.5 hours of vacation used, the extra .5 hour on Thursday would be paid so it does not fall under the <8 rule.
The closest I have come is adding a column and hiding it that takes the hours in each cell worked and subtract it from 8 but it adds the positive also. So instead of the total being -1.5 the total is -1.0
8-8 =0
7.5-8 =-.5
8-8 =0
8.5-8 =.5
7-8 = -1.0
--------------
-1.0

So
1. can this be done in a single column?
2. is there a way to omit positive number from the total?

Thanks
Tim

[oeldere]

E1 =sumif($c1:$c5,"<0")

[Funtreats]

Thank you oeldere

Now on to work on the rest of this sheet.

Tim

[oeldere]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

[Phuocam]

Or:

=SUMPRODUCT((B1:B5<>0)*(B1:B5-8))

[Funtreats]

Thank you Phuocam

[Funtreats]

Ok Even if I enter hours into the slots with text this does not seem to work. I would like to have 8 hours deducted if I enter PD or paid in the field when they take off.
It gives me a total but it is not accurate it should be 5x8 days off plus 6 for 54 hours off not 61.44 I even tried anchoring it with the $ signs.
To use the E1 =sumif($c1:$c5,"<0") I need to create a new column.
I have played with several different ways to make the formula work to no avail. In this sample we just need to total hours used (54) ignoring anything =>8


Oct.docx

[oeldere]

add an excelfile instead of a doc.

[Funtreats]

Sorry was not sure that a live file was allowed.

Test1.xlsx

[oeldere]

See if this is the right result

I16 = 46

If so I will add the formula's together.

[Funtreats]

Oeldere,

Yes that appears right, I had the sumif on it like that minus the anchor, but the text was messing me up.If I just changed the text to 8 it gave me the wrong answer.
I know it has to be something simple that I missed.
Something so simple in my mind and poof mind blown.

Makes me want to become an expert in Excel just so I can figure it out.

Tim

[oeldere]

And g16 houses the (total added) formula if you want to use that.

[Funtreats]

Wonderfull that should do it. I need to add a couple tweeks but am familiar on how that works.

The 8+8-sumif after the wild card I think is where I needed to look. I am sure anchoring the cells was also a big part.

Thanks so much again Oeldere.