If statement with multiple true values

[Gilk13]

Hello,

I have a 30 row spreadsheet. Data will be entered as an employee completes a job.
Column D is Job # (alphanumeric), and Column G is Amount

I have a total at the bottom of Job # that counts the number of claims if the cell is not blank.


I am trying to get the Amount to be 0, 20, or 25.

if the input cells in column D are blank then I want it to return a 0 for the Amount

If the job # count is 1-29, then I want it to return 20 for all Job #'s

If the job # Count is >= 30, then I want it to return 25 for ALL Job # (Jobs 1- infinity) .



The formula I have works fine until the count reaches 30, It does change the amount to 25 for all lines that have Job #'s but it also puts a 25 on the rows with no Job # (should be 0)

Here is what I have...

=IF(D32<0,0,(IF($D$42>=30,25,(IF(D32>0,20,0)))))

Thanks in advance for your help.

 

[BAlGaInTl]

=LOOKUP(D32,{0,1,30},{0,20,25})

Where D32 is your count.

 

[Gilk13]

Thank you for your quick response, but your formula is doing the same thing as mine......it's giving me 20 or 25, when the job # is blank and should be 0.

=LOOKUP(D32,{0,1,30},{0,20,25})

Where D32 is your count.

 

[Sancdar]

Really? The LOOKUP formula is working fine for me.

 

[BAlGaInTl]

It worked for me as well...

A blank should be treated as a 0 in that formula.

Where is the Job # located in relation to the formula?

Are you talking about something other than the count in D32?

 

[Gilk13]

The Job # would be enetered in D2 to D30. The Amount Column (G) would look first if there was a Job number in that row, if no, then 0, if yes then it should look at the count to determine the 20 or 25. Does that make better sense?


An employee gets paid $20 per job but if they reach 30 jobs then all jobs get paid at $25/job.

It worked for me as well...

A blank should be treated as a 0 in that formula.

Where is the Job # located in relation to the formula?

Are you talking about something other than the count in D32?

 

[BAlGaInTl]

I think I get it... and the total count is in $D$42 ?

Try this...

=IF(D32,LOOKUP($D$42,{0,1,30},{0,20,25}),0)

It checks to see if there is something in D32. If there is, then it looks at $D$42 and does the test based on that value.

 

[Gilk13]

Thank you, that is working for the most part....it does not work when the job# is alphanumeric but does work when it's all numeric
Is there an easy fix for that?

I appreciate your help.

I think I get it... and the total count is in $D$42 ?

Try this...

=IF(D32,LOOKUP($D$42,{0,1,30},{0,20,25}),0)

It checks to see if there is something in D32. If there is, then it looks at $D$42 and does the test based on that value.

 

[BAlGaInTl]

Try

=IF(D32<>"",LOOKUP($D$42,{0,1,30},{0,20,25}),0)

 

[Gilk13]

PERFECT!!!! Thanks so much!

Try

=IF(D32<>"",LOOKUP($D$42,{0,1,30},{0,20,25}),0)