While the standard SUM function works with numbers, you can combine it with text functions like LEFT and RIGHT to extract numerical parts from text strings and then sum those values. Here are a couple of scenarios:

Scenario 1: Summing Numbers with Text Labels

Imagine you have a column (let's say B) with entries like "Apples-10" or "Oranges-15". You want to sum the numerical values (10 and 15) but exclude the text labels ("Apples-" and "Oranges-").

Here's the formula using SUM and RIGHT:

`=SUM(RIGHT(B2:B10, 2))`

Explanation:

- RIGHT(B2:B10, 2): This part uses the RIGHT function to extract the last 2 characters from each cell in the range B2:B10. In our example, it will extract "10" and "15".
- SUM(...): The SUM function then adds these extracted numbers to get the total (25).

Scenario 2: Summing Numbers Separated by Text

Let's say you have a column (C) with data like "123 price 456". You want to sum the two numbers (123 and 456).

Here's the formula using SUM and LEFT & RIGHT:

`=SUM(LEFT(C2:C10, 3) + RIGHT(C2:C10, FIND("price",C2:C10)-1))`

Explanation:

- LEFT(C2:C10, 3): This extracts the first 3 characters (assuming your numbers are less than 3 digits).
- RIGHT(C2:C10, FIND("price",C2:C10)-1): This finds the position of the text "price" (using FIND) and subtracts 1 to exclude "price" itself. Then, it extracts everything to the right of that position, capturing the second number.
- + : The addition operator combines the left and right extracted values.
- SUM(...): Finally, the SUM function adds these combined values for each cell in the range.

Important Notes:

- These are just examples, adjust cell ranges and text separators based on your actual data.
- Ensure your text separators ("-" or "price" in our examples) are consistent for the formulas to work correctly.
- Consider using error handling functions like IFERROR to handle cases where the text separators might be missing.
- There might be alternative approaches depending on your specific data format.