Let’s say you have codes that have differing numbers of characters and you need to analyse them based on how many characters a code has. There is one function that can SUM and COUNT based on the number of characters in a code.

My favourite function for the last 15 years or so has been SUMPRODUCT.

Unfortunately the powerful version of SUMPRODUCT is not explained in Excel’s help system. The two examples here will provide a brief introduction to the undocumented power of SUMPRODUCT.

In the image below we have different codes in column A and we want to be able to count and sum the amounts in column B based on how many characters are in each code.

Part of the power of SUMPRODUCT is based on TRUE and FALSE.

TRUE and FALSE are typically associated with the IF function but they can be used in calculations.

TRUE equals one and FALSE equals zero.

Multiply any value by TRUE and it doesn’t change the value. Multiply by FALSE and you zero the value. These two simple statements have powerful implications when doing conditional counting or conditional summing.

The conditional functions SUMIF, SUMIFS, COUNTIF and COUNTIFS hav some limitations (discussed here).

The beauty of the SUMPRODUCT function is that it can use other functions within its brackets to perform more complex conditional calculations.

The image below shows the solutions to our summing and counting requirements.

The SUMPRODUCT function uses the LEN function, which returns how many characters are in a cell. But notice within the SUMPRODUCT function we are able to refer to a range and not a single cell in LEN function. This is the power of SUMPRODUCT. It allows you to do what is normally an array calculation but without having to do the array entry. Arrays are a whole other topic in Excel and I am not covering them in this article. Arrays are an advanced topic.

So the formula in cell E2 is

=SUMPRODUCT((LEN($A$2:$A$10)=D2)*1)

The SUMPRODUCT function allows you to calculate the length of each individual cell within the range A2 to A10 and compare that with the number in cell D2. This will give us a sequence of TRUE and FALSE results which are based on whether the code is one character long in this case. There is only one single digit code.

F9 function key

The F9 function key is a powerful key when learning functions and when trying to understand how functions work. You can select part of a formula in the cell or formula bar and press the F9 key. This will show you the result of that part of the formula. You must be careful and select a section of the formula that can be calculated independently. If you include an extra bracket then you may receive an error saying that what you selected can’t be calculated.

Analysis of formula in cell E2

The image below shows selecting part of the formula and then pressing the F9 key which will then display the result. I will progressively select a different part of the formula and then press the F9 key and you will see the results build until you see how the final figure is calculated. This will demonstrate how the SUMPRODUCT function works when counting.

The important part to understand here is that TRUE and FALSE are not converted to numbers until you do a calculation with them. So multiplying the TRUE and FALSE results by one, which we do at the end of the SUMPRODUCT, converts the TRUE into one and FALSE into zero. The SUMPRODUCT then adds up the results of the ones and zeroes.

F9 Warning

When using the F9 key like this, be aware that the formula is being replaced by the values. So you need to press Esc once you are done. Otherwise the results are hard-keyed into the formula. If you forget you can use Undo to reinstate the formula.

SUMMING

In the case of a sum result we don’t multiply the TRUE and FALSE results by 1, we multiply them by the values we want to sum.

Two important requirements when using SUMPRODUCT for summing.

1. The two (or more) ranges you refer to must have the same number of cells – and usually line up exactly. Both ranges used below start in row 2 and finish in row 10.
2. You CAN’T have a text entry in the range you are adding up – it will cause a VALUE error. So you can’t include headings in the range to add up.

The image below demonstrates how SUMPRODUCT for summing works.