Solving a Conditional Summing Text Problem in Excel

I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.

The image below has an example of the problem.

I want to add up the values in column B based on the length of the codes in column A.

I am using a SUM function with a condition based on the LEN function. The LEN function stands for length and counts the characters in a cell. This is a dynamic array solution. You can use the SUMPRODUCT function in place of SUM in older versions.

The formula returns an error because the text headings in column B are being multiplied. Multiplying text generates the #VALUE! error.

To get around that problem we can add two characters. The N function and the + plus sign.

We need the N function to convert the text in column B to zeroes.

The + plus sign gets the range to work with the N function. I have written about this before in the post below. It is a dynamic array issue.

The image below has the final formula.

The formula in cell B18 is.


This was copied down to the cells below.

The N function offers many solutions when text is a problem.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.