google sheets
as mention on the title, I am stuck to sum all the value inside multiple brackets within single cell in Google Spreadsheet.
Below is the query that must store within single cell:
26b(1), 26a(41), 27b2(17)
Basically, the value that I want is "59" ( sum of number 1, 41 and 17 ).
Note: if the query above is impossible to find, how about storing the value after equal such as 26b=1, 26a=41, 27b2=17
Instead of {"First Value;"Second Value"} write ={"First Value,"Second Value"} in cells D2 and D3 in the example provided in the question.
Then, instead of using a category names reference of the form D2 use the D2:D3 reference form.
Instead of {"First Value;"Second Value"} write First Value;Second Value in cells D2 and D3 in the example provided in the question.
Then, instead of D2 as the reference in the formula use =SPLIT(D2,";")
= or +) it is interpreted by Google Sheets as a string instead as an array. As the OP want to put one array in each row, instead of using a row separator, use a column separator.
As the array values are in a 1x2 range the reference to use in the formula should be of the form D2:E2. The formula will be like the following
=ArrayFormula(sum(sumif(A:A, D2:E2, B:B)))
In order to make easier to convert a string to an array it's suggested to remove the enclosing braces and the quotes, otherwise more functions will be required to remove them and will make the spreadsheet more complex and slow.
The formula will be like the following:
=ArrayFormula(sum(sumif(A:A, SPLIT(D2,";"), B:B)))
Here are your formulas:
B7 = A number
B8 =IF(B7 > 1.25,((B7-1.25)*2)+1.25,IF(B7 < 1.25,1.25-((1.25-B7)*2),"1.25"))
B9 =IF(B8 > 1.5,"1.5",IF(B8 < 0.5,"0.5",B8))
The problem is that at the end of the formula for B8 if the value in B7 is 1.25 you set the result to a string. Then the formula in B9 compares the string to 1.5 which isn't behaving as you expect. As an experiment change "1.5" in the formula to "Hello world" and B9 will always end up as "1.5" when B7 = 1.25.
These is the formulas you want:
B7 = A number
B8 =IF(B7 > 1.25,((B7-1.25)*2)+1.25,IF(B7 < 1.25,1.25-((1.25-B7)*2),1.25))
B9 =IF(B8 > 1.5,1.5,IF(B8 < 0.5,0.5,B8))
I just removed the quotation marks.
Best Answer
Formula
Brief explanation
We could use regular expressions on Google Sheets but it's support is limited. Fortunately Google Sheets has other features that could help to overcome those limitations.
The SPLIT function splits the cell value on A1 using a comma as separator. Using ArrayFormula makes REGEXTRACT able to take the SPLIT resulting array as argument and to apply the regular expression to each array member. The result is an array of texts, so VALUE is used to convert them to numbers and finally the SUM function sums those numbers.
From REGEXEXTRACT