SUMIFS using array as criteria

[Hateem]

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

Sumif using multiple partial matches

Hi All, Coming from a low-medium skill level with Excel, trying to get my head around the differences with LibreOffice Version: 6.0.4.2 (please be gentle). Likely a better way of doing this, however I have been maintaining a basic income/expense spreadsheet where I take ledger style dumps from multiple sources and then use an array containing key words/phrases to categorize entries. Structure of the formula in Excel is as follows; =SUM(SUMIFS(sum_range,criteria_range,{"*Transport*","*Courier*…

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!

[anon73440385]

Hello,

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: 6.3.4.2,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.

[Hateem]

Thank you very much. Works now!

Using:

Version: 6.3.4.2
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]

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?