I have cells that contain multiple strings delimited by commas.
Each string is comprised of the following 3 elements joined together:
I want to add the
number from each string that contains a particular
|1||1xExample, 2xAnotherExample, 5xThirdExample|
|2||1xAnotherExample, 3xThirdExample, 2xExampleFour|
For example, using the sample data above, if the
substring I'm trying to match is
AnotherExample then the formula would return
AnotherExample appears once in
A1 preceded by a
2 and it also appears once in
A2 preceded by a
1. So adding the
numbers from those two strings gives
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 all the strings that contain a
substring and then take the
number from each of those strings and add them together?