Libre Calc: SUM where the criteria are comma-delimited values but in a single cell

[LNT]

Hi there,

I am not the most advanced user of Libre Calc (7.6) but most of the time I find solution to satisfy my requirements. Nevertheless the solutions I apply might not be the most elegant nor the best but they worked so far.

I have the following task at hand - I could not manged to find a solution for though.

I have a column (A) containing numeric values separated by comma (as a result of a textjoin function).
Now I want to take that list of comma delimited numeric IDs and lookup each IDs amount and have all of them sum up in column (B).

Imagine the IDs with their values are contained in a separate sheet.

Wanted result:
A: 1,3,5 couple IDs (1:n) separated by comma
B: 500.00 sum of values related to the IDs above (sum of the amounts of ID1, ID3 and ID5)

other sheet
1 100
2 125
3 300
4 270
5 100

How can I archive this with a SUM formula?

Thank you,
LNT

[erAck]

Under Tools → Options → Calc → Calculate, Formulas Wildcards activate Enable regular expressions in formulas.

Then, with your data in Sheet2.A2:B6 and the ID string in Sheet1.A1 enter in Sheet1.B1:

=SUMIF(Sheet2.A2:A6;"^"&SUBSTITUTE(A1;",";"|")&"$";Sheet2.B2:B6)

[LNT]

Thanks - erAck,

I set the options (it was already enabled) and tried your formula.
While it yields no error it only produces only a zero as a result.

Any ideas?

Thanks,
LNT

[Lupp]

Study this attached example (15.5 KB)

Hints.
I didn’t understand your usage of the terms “criteria” and "ID.
The comma shouldn’t be used as the list delimiter for lists where elements need to be interpreted as numbers. The majority of locales worldwide use the comma as the decimal delimiter. It’s also often misused as a “thousands separator”.

The example data are randomly generated using RAND.NV() and RANDBETWEEN.NV(). Rather old versions (and any AOO) don’t know these functions.

[LNT]

Excellent example! Thanks!

I just needed to adjust a the formula to something like this

=SUMPRODUCT($Data.$C$2:$C$31;ISNUMBER(FIND("," & $Data.$A$2:$A$31 & "," ; "," & $A4 & ",")))

As I have a cell with the search.look up criteria as values in a list separated by a comma ‘,’ instead of ‘;’.
But it does the trick well.

As for the ID - I should have been more clear about. The example I gave containes integer but it could have been alphanumeric values indeed. So think of it as alphanumeric IDs - so I see the delimiter comma just being fine here in this application (and it is a given here).

Criteria refers to the whole single cell containing the IDs separated by comma.
Sorry for being a little vague here.

Thank you!
LNT

PS. Sorry for the poorly formatted text but currently it seems broken (looking fine in edit modus but then goes than a little off).

[Lupp]

Thanks for the thanks.
There is an enhanced example (25,9 KB).
The new example contains two sheets with solutions of which I wouldn’t assume they are closely related to the current question.
I mainly made them to back my claim that calls to Calc functions returning arrays can be useful as subexpressions without any need to lock an output range. To do so I needed to include a UDF because Calc still hasn’t a TEXTSPLIT() function. It’s used only in the last sheet. Sheet Eval2 will also work if document macros are not permitted.

[mariosv]

TEXTSPLIT function is already in master, the future version 25.2.