How to sum all the value based on single key after equal symbol within single cell in Google Spreadsheet

Question

as mention on the title, I am able to sum based on single key after equal symbol all the value within the single cell as shown below.

Thanks to @Emmabee, I was able to get the total of that specific key.

=arrayformula(sum(if(iferror(find(concat(B1,"="),transpose(split($A2,", "))), 0)=1,filter(split(transpose(split($A2,", ")),"="),{FALSE,TRUE}),0)))

Answer 1

=arrayformula(sum(if(iferror(find(concat(B1,"="),transpose(split($A2,", "))), 0)=1,filter(split(transpose(split($A2,", ")),"="),{FALSE,TRUE}),0)))

This formula assumes the data to be summed is in cell A2, and that the "key" is stored in cell B1.

---

Explanation:

To display this messy formula more clearly:

=arrayformula(
   sum(
      if(
         iferror(
            find(concat(B1,"="), 
               transpose(split($A2,", "))), 0)=1,
         filter(
            split(
               transpose(split($A2,", ")),"="), {FALSE,TRUE}
         ), 0
      )
   )
)

What each function is doing in this formula specifically:

ARRAYFORMULA() This is here to allow arrays to be used and returned in non-array functions, specifically IF() and SPLIT(). This is how we're able to recursively handle each cell in the range one at a time without the luxury of helper cells to split the keys from their values.

SUM() Most of this formula is carefully creating an array whose only elements are the values which follow the key. The SUM() function is the nice simple ending, taking that curated array as its range and summing all the values.

IF() Since we don't have actual cells to work with, we need to use the IF() function. With the help of ARRAYFORMULA() we are able to make this work essentially like a for-loop in programming would. It loops over an array of variables, checking each one against the specified condition and then returning the specified value. In our case, checking to see if the text before the "=" is our key, and returning the text after the "=" if it is.

FIND() This function allows us to check each cell to make sure the key is present. The function will return the location where the specified string was found. If the string was the first thing written in the cell the returned value will be 1, and since that's all we care about here, that's all we're going to check. (Without this check searching for the key "6a" would also find any data tied to key "26a" or "9375486a".)

IFERROR() If the FIND() function can't find the specified string at all, instead of returning a numerical value it will return an error. The error throws a wrench in our plans since the rest of the formula won't accept it, causing us to get an error message any time at least one cell in the range does not contain the specified key. So.. literally 100% of the time. We're using this function to replace the error with a 0, though as long as we don't replace it with a 1 it doesn't matter what number we pick.

CONCAT(B1,"=") This is the other half of protecting the data from similar keys. Appending the equals symbol to the end of the key before we search for it ensures that it's the last thing written before we get the value the key is tied to. (Without this check searching for the key "6a" would also find data tied to key "6aa" or "6ab". These precautions also protect our data from purely numeric keys which may also appear as values. Otherwise searching for the key "12" would also find something such as "26a=12" even though the 12 here is not a key.)

Together these last three functions return a value of TRUE whenever the key we've been given and the key in the cell we're looking at are exactly identical, and FALSE otherwise, which gives us the first parameter in our IF() function.

SPLIT() The SPLIT() function is the other one being given special functionality by the ARRAYFORMULA() function. In our formula it's doing two things. First it's splitting the data in the single cell into a single row with each key/number pair as a separate entry. The other thing it does is accept this split data TRANSPOSED() into a column instead of a row and returning a matrix with two columns. The first column will contain the key, and the second will contain the data after the equals sign.

FILTER() Since the second use of the SPLIT() function is only being performed on cells which return a value of TRUE in our IF() function, we already know that each item in the first column will be our chosen key. The examples you gave all have the key as a string, meaning we could just leave them and let the SUM() function at the end ignore them. However, I wanted to make sure this formula would still work for you if you found the need to use purely numeric keys. We use FILTER([range], {FALSE,TRUE}) to drop the first column and only keep the values in the second column. Otherwise the numeric value of the key would be added to the result for each time it appeared.

Then finally we add ", 0" before we close the parentheses on our IF() function, so that cells without the key will contribute a 0 to the final sum instead of FALSE. Why? I don't know, it doesn't really matter, but it's only three extra characters and I think it's good manners to only give numbers to a SUM() function.

Close up the rest of the parentheses, and we're done!