multiple cells into 1 IF cell

[gabbie1977]

I can't seem to be able to add more cells to my IF, i keep getting errors.

I want to take from D3 to D30, if the value exceeds 31999 do this, if the value is under do this.

So far this is what i have tried:

=IF(D3:D31>=31999,"OVER LIMIT","Under Limit")

^ that one generates #value error

<table border="0" cellpadding="0" cellspacing="0" width="101"><colgroup><col width="101"></colgroup><tbody><tr height="18"> <td class="xl64" style="height:13.5pt;width:76pt" height="18" width="101">
</td></tr></tbody></table>=IF(D3>=31999,"OVER LIMIT",IF(D4>=31999,"OVER LIMIT",IF(D5>=31999,"OVER LIMIT",IF(D6>=31999,"OVER LIMIT",IF(D7>=31999,"OVER LIMIT",IF(D8>=31999,"OVER LIMIT",IF(D9>=31999, "OVER LIMIT",IF(D10>=31999, "OVER LIMIT","Under Limit"))))))))

^ i cannot go over D10 cell using this method

 

[ukmikeb]

Hi

Welcome to the forum.

Try this -

=IF(MAX(D3:D31)>=31999,"OVER LIMIT","Under Limit")

hth

 

[gabbie1977]

Hi

Welcome to the forum.

Try this -

=IF(MAX(D3:D31)>=31999,"OVER LIMIT","Under Limit")

hth

It works thanks, i always tend to over complicate my formulas lol

 

[ukmikeb]

Hi

Pleased to have helped you solve your problem.

Thanks for the feedback.

You'll find this forum is a fantastic resource to improve your Excel skills.

 

[Rick Rothstein]

Another method to consider...

=IF(COUNTIF(D3:D30,">=31999"),"OVER LIMIT","Under Limit")