Explanation
In this example, the goal is to sum the values in columns C, E, G, and I conditionally using the text values in columns B, D, F, and H for criteria. This problem can be solved with the SUMPRODUCT function , which is designed to multiply ranges or arrays together and return the sum of products. The formula in K5 is:
=SUMPRODUCT(--($B5:$H5=K$4),$C5:$I5)
Working from the inside out, SUMPRODUCT is configured with two arguments, array1 and array2 . The first array, array1, is set up to act as a “filter” to allow only values that meet criteria:
--($B5:$H5=K$4)
Note: both references above are mixed references . $B5:$H5 has the columns locked, so that the formula can be copied across. K$4 has the row locked so that the formula can be copied down.
All values in the range B5:H5 are compared to the value in K4. The result is an array of TRUE and FALSE values like this:
{TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE}
In this array, each TRUE value corresponds to the value “A” in B5:H5, and FALSE values correspond to other values. The double negative (–) is used to convert the TRUE and FALSE values to 1s and 0s and the result looks like this:
{1,0,0,0,1,0,0}
We can now simplify the formula like this:
=SUMPRODUCT({1,0,0,0,1,0,0},$C5:$I5)
The second array, input as $C5:$I5, evaluates to:
{2,"B",5,"A",2,"B",1}
We can now simplify the formula to:
=SUMPRODUCT({1,0,0,0,1,0,0},{2,"B",5,"A",2,"B",1})
Next, SUMPRODUCT multiplies the two arrays together. Only the values in the second array that correspond to 1s in the first array survive this operation. Since SUMPRODUCT is programmed to ignore the errors that result from multiplying text values, the final array looks like this:
{2,0,0,0,2,0,0}
SUMPRODUCT then sums the array and returns a final result of 4.
=SUMPRODUCT({2,0,0,0,2,0,0}) // returns 4
This represents the numbers that correspond to “A’” in row 5. As the formula is copied down and across, we get a sum for “A” and “B” in each row.
Explanation
In this example, the goal is to return the total for an entire column in an Excel worksheet. One way to do this is to use a full column reference.
Full column references
Excel supports " full column " like this:
=SUM(A:A) // sum all of column A
=SUM(C:C) // sum all of column C
=SUM(A:C) // sum all of columns A:C
You can see how this works yourself by typing A:A or C:C into the name box (left of the formula bar ) and hitting return. You will see Excel select the entire column.
Example
To solve the problem in the example worksheet, we can use a full column reference to column D with the SUM function like this:
=SUM(D:D)
The result is the sum of all numeric values in column D. One advantage to full column references is that they will automatically include new data. As entries are added to the table, the formula will automatically include the new amounts.
Advantages and risks
The main advantage to full column references is simplicity. Simple and very compact, a full column reference will automatically include all data in a column, even as data is added or removed. However, full column references come with certain risks. One risk is that you may accidentally include extra data in a calculation. For example, if you use =SUM(A:A) to sum numbers in column A, you are targeting over 1 million cells. If column A includes forgotten dates somewhere far below in the worksheet, the numeric value of these dates will be included, and SUM will return an incorrect result. Another risk is performance. Because so many cells are included, full column references can cause severe performance problems in certain worksheets or formulas.
Alternatives
There are good alternatives to full column references. If you need to target data that may change in size, a good solution is an Excel Table , which will automatically adapt to changing data. Another option is to use a dynamic named range .
