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.
=SUM((LEN($A$2:$A$14)=A18)*N(+$B$2:$B$14))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.