Ask Your Question
1

Sumif using multiple partial matches

asked 2018-06-21 07:00:34 +0100

brashquido gravatar image

updated 2018-06-21 07:01:01 +0100

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*"}))

Essentially any entry with a description containing Transport of Courier would have the dollar amount of that entry added to the Transit costs category in my main sheet.

Have tried this in LibreOffice and I cannot seem to get it to work. I read something about Arrays not being able to be nested. If this is the case, what would be the best way to sum the numbers in one column when a partial match on multiple criteria is found in another column?

Really appreciate the assistance.

edit retag flag offensive close merge delete

Comments

"Transport of Courier " - I assume in my answer that "Transport or Courier" was intended.

Jim K gravatar imageJim K ( 2018-06-21 10:14:47 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-06-21 10:10:08 +0100

Jim K gravatar image

updated 2018-06-21 10:22:30 +0100

The criterion can be a regular expression, as explained in the SUMIF help page. There is no need for arrays or two functions.

=SUMIF(A2:A5;".*Transport.*|.*Courier.*";B2:B5)

Breakdown:

  • A2:A5 is the criterion range
  • .* means match any character zero or more times
  • | means match the first or the second expression
  • B2:B5 is the sum range
edit flag offensive delete link more

Comments

Thanks for this, works a treat. Just had to change the default of allowing wildcards to allow regular expressions.

brashquido gravatar imagebrashquido ( 2018-06-22 11:36:48 +0100 )edit

Glad it helped. Please mark the answer correct, as explained under guidelines for asking.

Jim K gravatar imageJim K ( 2018-06-22 15:05:23 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-21 07:00:34 +0100

Seen: 435 times

Last updated: Jun 21 '18