0

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.

enter image description here

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)))
  • Where will the key be stored? Is that in another cell? Also the sum of 1 and -17 is -16, not 16. Did you want the absolute value or was this just a typo? – Emmabee Jun 13 '18 at 15:17
  • Also, if you don't mind me asking, why does this have to use a single cell? It can be done in a single cell but it is going to be a very ugly formula and editing it at a later date to add/remove/change elements will be a massive chore. – Emmabee Jun 13 '18 at 15:21
  • @Emmabee Q1 yes, it was just typo. The value should be -16. I have edit the question and attached photo so it is not confusing. :) – D.D Jun 15 '18 at 4:33
  • @Emmabee Q2 It must be in single cell coz that is the requirement. – D.D Jun 15 '18 at 4:33
  • @Emmabee Q3 I have a formula which work to sum all the value. Now all i need is just to filter the key ( such as 26b ) before sum the value. – D.D Jun 15 '18 at 4:35
2
=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!

  • I tried the formula and it did not work. I have rewrite the question so it is not as confusing as before. Thanks – D.D Jun 15 '18 at 4:32
  • @D.D Yeah, I'd assumed the data itself was at least in a column instead of all jammed in one cell. It's an easy fix, transpose(split(A2,", ")) would be used to turn the single cell into the columns my formula assumed we already had, so just replace all occurrences of A1:A with that. I've edited my answer to reflect that. – Emmabee Jun 15 '18 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.