Just as Excel can be used in many different ways, it can also be used to sum columns based on adjacent criteria. In this post, we’ll consider how to sum columns using adjacent criteria with a formula approach in Excel. To be able to do this, you need to get familiar with SUMPRODUCT function.
Sum columns based on adjacent criteria
To sum up columns based on adjacent criteria, a formula based on the “SUMPRODUCT” function can be used.
Figure 1. Sum columns based on adjacent criteria
In figure 1 above, the formula in cell I6 is:
The formula in cell I6 we can split as below:
Since SUMPRODUCT is specific to overlook errors that arise from multiplying text values, the final array then looks thus:
The only values that “survive” the multiplication are the ones that match 1’s in array1.
The formula uses SUMPRODUCT to first multiply and then sum up the products of the arrays. We have 2 arrays in the formula. $C6:$G6
Array1 – Acts as a “filter” to only allow values that fulfill the condition. It uses a range that starts in the first column containing values that must fulfill the condition. These values are in a column, to the left of, and directly adjacent to the data values. The criteria are applied as a basic test that produces an array of FALSE and TRUE values:
The formula above tests each of the values in array1 using the provided criteria and then uses the double negative (–) to force the resulting FALSE and the TRUE values to 0’s and 1’s respectively as shown below:
Note that the 1’s matches columns 1,3, and 5. This fulfills the criteria of “Y”.
Array2 – It uses a range that is “moved” to the right by a column. This range begins with the first column and terminates with the last column containing values to sum.