Ask Your Question

How to use Multiple Texts or Numbers under IF

asked 2019-07-06 15:55:39 +0200

CSTIG gravatar image

updated 2020-09-26 11:56:04 +0200

Alex Kemp gravatar image

=IF(C12="1,2,3,4" 50+B7+B8*1.25)

The part in Bold is where I have the issue. I need IF() to cover multiple Texts/Numbers because typing,

=IF(C12="1" 50+B7+B81.25) =IF(C12="2" 50+B7+B81.25) =IF(C12=3" 50+B7+B81.25) =IF(C12=4" 50+B7+B81.25)

Up to 50 with slight difference for each 5 levels, but Libreoffice won't allow that so I need to know how to type a shortened, compact, version.

I've tried

=IF(C12="1,2,3,4" 50+B7+B8*1.25)

=IF(C12="1:4" 50+B7+B8*1.25)

=IF(C12="1-4" 50+B7+B8*1.25)

but all those throw off errors.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-07-06 16:41:57 +0200

Hello @CSTIG

There are various options, just few of them:

  1. Use OR() function to return True when at least one of the test conditions returns True: =IF(OR(C12=1;C12=2;C12=3;C12=4);"Ok";"Wrong")

  2. Use AND() function to return True when every test condition returns True: =IF(AND(C12>=1;C12<=4);"Ok";"Wrong") This will return Okfor every number larger or equal than 1 and smaller or equal then 4, also decimal

  3. Use SUMPRODUCT()and array of numbers: =IF(SUMPRODUCT(C12={1;2;3;4});"Ok";"Wrong")

  4. To make formula shorter in case of many array elements, you can use Named Ranges to reference array values in formula. Go to menu item Sheet -> Named Ranges and Expressions -> Define, create new range with values {1;2;3;4}and name it for example TestNumbers. Then the formula will be like =IF(SUMPRODUCT(C12=TestNumbers);"Ok";"Wrong")

edit flag offensive delete link more

answered 2019-07-06 16:32:34 +0200

Opaque gravatar image

updated 2019-07-06 16:38:24 +0200


how about (If I understand your C12 = "1,2,3,4" right that you mean the calculation should occur if C12 contains any of these four values and not the string "1,2,3,4").
=IF(OR(C12="1",C12="2",C12="3",C12="4"),50+B7+B8*1.25,"anything if false") ?

Pleas note: your formulas miss the argument delimiter "," (depends on locale and may be ";" in your locale)

If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-07-06 15:55:39 +0200

Seen: 178 times

Last updated: Jul 06 '19