SUMIFS using array as criteria

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!


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 (:heavy_check_mark:) next to the answer.

Thank you very much. Works now!


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

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?