SUM not calculating with IF function in column

[Char]

I have an employee that I pay per day, instead of per
hour. However, I am still tracking actual hours worked.
If they come in late, or need to take off a few hours,
they are paid for 1/2 a day. I have created a time sheet
that has the following columns: "Time In", "Time
Out", "Hours Worked", "Total Days". The "hours worked"
has a formula to calculate the actual hours worked.
Then "Total Days" is an IF formala. The IF formula is as
follows:

IF(E17>4,"1.00",IF(E17>0,"0.50",IF(E17=0,"0.00")))

This formula works fine; however, I have at the bottom
of "Total Days" I want a SUM of the days. My SUM formula
is not working.

Any suggestions????

Thanks.

 

[Paul]

I have an employee that I pay per day, instead of per
hour. However, I am still tracking actual hours worked.
If they come in late, or need to take off a few hours,
they are paid for 1/2 a day. I have created a time sheet
that has the following columns: "Time In", "Time
Out", "Hours Worked", "Total Days". The "hours worked"
has a formula to calculate the actual hours worked.
Then "Total Days" is an IF formala. The IF formula is as
follows:

IF(E17>4,"1.00",IF(E17>0,"0.50",IF(E17=0,"0.00")))

This formula works fine; however, I have at the bottom
of "Total Days" I want a SUM of the days. My SUM formula
is not working.

Any suggestions????

Thanks.

Don't put the possible results in double quotes - this makes them text
rather than numbers, and you can't sum text!
Also, the third IF is not needed.
=IF(E17>4,1,IF(E17>0,0.5,0))

 

[J.E. McGimpsey]

Remove your quotes - they return strings, which SUM() ignores.

You could also accomplish the same thing with:

=0.5*((E17>0)+(E17>4))

 

[Tim C]

Remove the "quotes" from your formula. They cause the numbers to go in as
text instead of numbers, and text is ignored by SUM.

Tim C

 

[Anders S]

Hi,

Your results can not be summed because they are text, not numbers.

Try
=IF(E17>4,1,IF(E17>0,0.5,IF(E17=0,0)))
and set the cell format to two decimals.

HTH
Anders Silvén

 

[Char]

Thanks for ALL of your help! I removed the quotes &
everything is adding up just fine! I can't believe I did
not remember the "quote" thing since I have done VBA
programming. Thanks again.

Char
:O)

 

[SFry]

Just stumbled across this solution.. amazing! So simple and i did not know that, have spent hours trying to re-format the cells,
Thanks