Ask Your Question

OR inside SUMIF

asked 2020-07-31 00:34:28 +0200

Nikola gravatar image


I entered this formula in CALC:


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

Sincerely, Nikola

edit retag flag offensive close merge delete


Concerning the separator between arguments (parameters), please consider to also read .

Lupp gravatar imageLupp ( 2020-07-31 01:02:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-07-31 00:54:21 +0200

Lupp gravatar image

updated 2020-07-31 01:16:55 +0200

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.

edit flag offensive delete link more


That was useful, thank you very much!

Nikola gravatar imageNikola ( 2020-07-31 05:39:51 +0200 )edit

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

LeroyG gravatar imageLeroyG ( 2020-07-31 17:46:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-31 00:34:28 +0200

Seen: 28 times

Last updated: Jul 31