0

I have a sheet that contains cells that have lists of data within them, for example: One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample The cell below contains: 1xAnotherExample, 3xThirdExample, 2xExampleFour And so on..

If I want to calculate the total of "AnotherExample", which in the two example cells above would be 3 then how would I go about doing this? I've tried a few different things and the closest I can get is a formula which takes all numbers in the cell and adds them together instead of taking a specific number, is there a way for a formula to find specific text and then lookup the number before that string of text and add it together?

Such as: Finding "xAnotherExample" in Cell C1, look 1 character left, get number. This way it would ignore the other values in the cell and only get the one I want, then add that from each cell containing "xAnotherExample" and output the total.

Any help would be much appreciated.

Improve this question
2
  • Please share a link to a sample spreadsheet with realistic data in it (at least five rows), along with the hand-entered results that you'd like to see a formula produce for that sample set. The closer to your real data you can make the sample data, the better.
    – Erik Tyler
    Jan 21 at 19:53
  • Hi and welcome to WebApps. Such as: Finding "xAnotherExample" in Cell C1... - that sounds like a good approach. What result did you get when you tried that?
    – Tedinoz
    Jan 22 at 2:45

1 Answer 1

Reset to default
0

Use regexextract(), like this:

=arrayformula( 
  sum( 
    iferror( 
      regexextract(A2:A, "(\d+)xAnotherExample") + 0 
    ) 
  ) 
)
Improve this answer

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.