MrExcel.com - Your One Stop for Excel Tips & Solutions

=If(Sum( "Question"

Posted by Jo on December 28, 2001 7:16 AM

Here is my formula.

=IF(SUM('Daily Hours'!L5)<50, SUM('Daily Hours'!L5),"")

I just want to know if I can change this somehow so that if the number is under 50 but over 40. I have tried several ways but can't seem to get it to work.
Thanks

Posted by Scott on December 28, 2001 7:24 AM

Try this:

=IF(SUM('Daily Hours'!L5)<50,IF(SUM('Daily Hours'!L5)<40,SUM('Daily Hours'!L5),""),"")

Posted by If I read you right - Adam S. on December 28, 2001 7:24 AM

=if(and(L540)=true,L5,"")

Out of curiosity, why did you use the word "SUM" in your formula? Did you mean to have L5 represent a range? If so:

=If(and(sum(data)<50,sum(data)>40)=true,sum(data),"")

Hope that helps out
Adam S.

Posted by Adam S. on December 28, 2001 7:26 AM

hey my post went a bit screwy - oops

Posted by Joe Was on December 28, 2001 7:30 AM

=IF(AND(SUM('Daily Hours'!L5)<50,'Daily Hours'!L5>40),SUM('Daily Hours'!L5),"")

Note: this will give the values: 41, 42, 43, 44, 45,...49 only! JSW

Posted by Jo on December 28, 2001 9:08 AM

The reason I used SUM

I am taking the total from another sheet that is the sum of that row. Someone else taught me how to do this, so if it's wrong let me know the right way.
Thanks

Posted by IML on December 28, 2001 9:30 AM

Boolean

There is no need to use the sum, as adam suggest. The sum of one cell is the same as just the cell.

Another formula you could use is
='Daily Hours'!L5*('Daily Hours'!L5 > 40)*('Daily Hours'!L5 < 50)