How can I sum a column with cells containing text?

I have an Excel sheet containing nearly 6000 products. In the Quantity column, there are some values ending with "60pcs", and some others with "7pkts".

I need to take the total quantity at the end and am unable to do that due to those letters mixed with numbers. How do I calculate the total sum if the values contain text?

Solution 1:

This is less general than @Andi Mohr's solution; it assumes you have just those unit measures, both starting with "p". The only advantage is that you get final result with one formula.

=SUM(NUMBERVALUE(MID(A1:A6000,SEARCH("p",A1:A6000)-1)))

Adjust column references as needed.

It has to be confirmed by pressing Ctrl+Shift+Enter.

Solution 2:

This rather brilliant formula will do the job for you.

If your mix of numbers and text is in cell A1, enter this in the next cell, pressing Ctrl+Shift+Enter:

=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))

You can then sum this column to calculate your total.

This formula was originally posted to Google Groups by somebody called Lori - I read about it on a Chandoo forum thread from a post by Sajan. How it works:

The magic of NPV is the NPV calculation formula, where each term is multiplied by the inverse of (1+rate)^n, where n is the nth term in the series. e.g. (1+rate)^1, (1+rate)^2, etc. By using different values for rate, we can get different results. In this case, using -0.9 gives us 1+rate=1+-0.9=0.1. So we get values like {0.1;0.01;0.001;0.0001;0.00001}. Taking the inverse of this gives us {10;100;1000;10000;100000} etc. Combined with the fact that NPV skips text values, we get the desired results.

Edit: Improved Calculation Speed

Máté Juhász suggested an addition to speed up the formula if applied to a large range. It checks if the string is already a number first, to save Excel time calculating something it doesn't need to.

=IF(ISNUMBER(A1),A1,NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,"")))