Ask Your Question
0

OR inside SUMIF

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

Nikola gravatar image

Hello,

I entered this formula in CALC:

=SUMIF(B4:B2379,OR("=2017","=2018","=2019"),D4:D2379)

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

Sincerely, Nikola

edit retag flag offensive close merge delete

Comments

Concerning the separator between arguments (parameters), please consider to also read https://ask.libreoffice.org/en/questi... .

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

1 Answer

Sort by » oldest newest most voted
0

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

Comments

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

Stats

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

Seen: 28 times

Last updated: Jul 31