CODEWITHSUNDEEP
excelexcel-formula
Najoua
Najoua

Reputation: 79

Sum several values between brackets separated by line break in Excel

I would like to sum several values between brackets that are separated by line breaks in the same cell, where there are other characters.

I have tried the suggested formula here but it didn't work.

I have macOS Montery and I am using Microsoft Excel for Mac Version 16.16.27 (201012)

My Excel cell looks like this (I have many other cells, and the number of lines differ in each cell):

enter image description here

So in this case, the sum wanted is 1+2+2+3+1+3

Thank you,

Najoua

Upvotes: 0

Views: 114

Answers (1)

JvdV
JvdV

Reputation: 75860

Try:

enter image description here

Formula in A3:

=SUM(--INDEX(TEXTSPLIT(A1,{"(",")"},CHAR(10)),,2))

Or, for Excel 2013 (and higher):

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",")"),CHAR(10),")"),")","</s><s>")&"</s></t>","//s[position() mod 3 = 2]"))

Upvotes: 3

Related Questions