Ask Your Question

SUMIFS using array as criteria

asked 2020-01-29 12:37:15 +0200

Hateem gravatar image

I want to sum column 14 where column 2 is equal to a specific value and column 10 either of 3 values. I tried the one similar to what I did in MS Excel but it's not working. I also tried the one specified in this link

but still failes. It fails to the 2nd criteria.

These are the samples I tried:

=SUMIFS(INDEX(Deliveries,,14),INDEX(Deliveries,,2),"0043", Index(Deliveries,,10), {"Unpaid", "Partial", "Bad"}) =SUMIFS(INDEX(Deliveries,,14),INDEX(Deliveries,,2),"0043", Index(Deliveries,,10), "Unpaid|Partial|Bad") =SUMIFS(INDEX(Deliveries,,14),INDEX(Deliveries,,2),"0043", Index(Deliveries,,10), "Unpaid|Partial|Bad")

Please help, thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-01-29 13:15:53 +0200

Opaque gravatar image

updated 2020-01-29 13:16:25 +0200


If you set Tools -> Options -> LibreOffice Calc -> Calculate -> Cat: General Calculations -> Option: [x] Enable regular expressions in formulas then your already tested solution:

=SUMIFS(INDEX(Deliveries;;14);INDEX(Deliveries;;2);"0043"; INDEX(Deliveries;;10); "Unpaid|Partial|Bad")

should work.

Tested using LibreOffice:

Version:,Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US,Calc: threaded

Hope that helps.

If the answer helped to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more


Thank you very much. Works now!


Version: Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa CPU threads: 4; OS: Mac OS X 10.14.6; UI render: default; VCL: osx; Locale: en-PH (en_PH.UTF-8); UI-Language: en-US Calc: threaded

Hateem gravatar imageHateem ( 2020-01-29 13:35:16 +0200 )edit

Big caveat, though, it would take around 30 seconds to switch views between sheets that are less than 10k rows. Ended up switching back to Disabling both expressions and wildcards in formulas just to lessen that freezing moment in shifting sheets down to 2 to 3 seconds.

Any ideas why this slooo....o..o..oo....w speed?

Hateem gravatar imageHateem ( 2020-01-30 13:14:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-29 12:37:15 +0200

Seen: 44 times

Last updated: Jan 29