Formula SUMIF assistance

[TQB]

Hello,

I am looking for advice on how to write this formula, unfortunately I only have access to excel at work so it makes it difficult to pound these sort of things out on my off time. Anyway.

What I need to accomplish is to take the sum of let's say I6 which happens to be the total number of hours per week, and if it is over a certain number minus that number and have the answer in C6. And I do know that the formula will have to be placed in C6.

I have done some "homework" on this but I dont know if this formula would work or if there is a better way to do it. I am not even sure if you can take just the value and include that as a working part of the formula such as : =SUMIF(I6>"20"), C6=I6-"20)

I would input example here but the spacing is all off so it makes it that much more confusing. Sorry.


So (I) is 2.75 hours over the max so what I would like to do is have that input automagically appear in (C) of the same row.


Thanks in advance for any assistance on this query,


TQB

[duane]

how about

=max(0,i6-20)

in c6

so c6 is 0 unless i6>20

[TQB]

Thanks duane, I will have to try that one out on monday I appreciate your quick response.


TQB

[pinmaster]

Hi
One way:

=IF(I6>20,I6-20,"")
this will leave the cell blank if I6 is under 20, you could also put text inside the double quotes if you wish... i.e. =IF(I6>20,I6-20,"Ok")

or
=IF(I6>20,I6-20,0)
to display a zero if I6 is under 20

Hope that helps!
JG

[TQB]

I appreciate your help, unfortunately none of these formulas are working for me.
Is there another way to do this? I have entered every combination of the formulas you both have given and one or two of my own but to no avail. It makes every bit of sense that it should work but it just returns one of two things depending on the order that it is entered. 1. #NAME? 2. circular reference. I am frustrated this should be an easy thing.


grr

TQB

[pinmaster]

Hi
This is very strange because there is absolutetly no reason why my formula would return #NAME? or a circular reference. Try this, copy the formula below and paste it directly in cell C6, if done correctly it should work.

=IF(I6>20,I6-20,"")

JG