OR inside SUMIF


I entered this formula in CALC:


I get #VALUE! as a result. What is wrong?


Concerning the separator between arguments (parameters), please consider to also read Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet? .

If the spreadsheet document is set to use RegularExpressions, and B4:B2379contains numeric strings or numbers which will be automatically converted, the formula =SUMIF(B4:B2379;“2017|2018|2019”;D4:D2379) would do.

The OR function is not usable in this case because it is not capable of retujrning an array.
However there is a way to replace it with a bit of arithmetic and a single comparison on the first parameter position: =SUMIF((B4:B2379=2017)+(B4:B2379=2018)+(B4:B2379=2019) >0;TRUE();D4:D2379)
This version is supposed to be less efficient, butt it will also work if the option to use RegularExpression is disabled.

{=SUMIF(ISNUMBER(FIND("!" & B4:B2379 & “!” ; “!2017!2018!2019!”)) ; TRUE() ; D4:D2379)} is also an option.

That was useful, thank you very much!

@Nikola, Don’t forget to check the mark (Correct answer mark) to the left of the answer to show that your question was solved.