I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.

The table below shows the solution that works in all versions. The SUMPRODUCT function is very versatile and can add up text number when most functions ignore them.

Multiplying by 1 converts the text numbers into real numbers.

With dynamic arrays the SUM function can do the same calculation.

You can see how this works in the image below.

When you select part of a formula that can be calculated or displayed in isolation, pressing the F9 function key shows the results.

The image above shows how the text numbers (enclosed in quotation marks) are converted into real numbers for the SUM function to add up.

This works even if there is a mix of text and real numbers in the range.