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?

  • On a note, you might be interested in custom formats for the future; you could add a string to what is visible, without changing numeric cell content. Custom Formatting for an ex. – SΛLVΘ Oct 29 '15 at 13:27
  • 1
    Of your 6,000 products, how many have values that contain text? Are you will to modify the sheet structure? I ask because the best way to ensure you get accurate summation for this column is to move the text to another column. The package type really should be in the column of its own. Formulas may help in the short term, but can allow miscalculation in some cases. – CharlieRB Oct 29 '15 at 14:24

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.

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)%,"")))
  • It's really an elegant formula, however I'm afraid it's too complex for the current problem and for bigger dataset it quickly will become slow. You can apply it on numbers too, but checking whether the original value is number (=IF(ISNUMBER(A1),A1,NPV(...))) would make your formula reasonably faster, even because majority of values doesn't need to be converted. – Máté Juhász Oct 29 '15 at 13:40
  • I'm not sure it's too complex - all you have to do is copy, paste and change the cell reference. Often people aren't too worried if they don't understand the formula. But take your point on speeding it up, I'll add that in. – Andi Mohr Oct 29 '15 at 14:41

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.