< Go Back

Sum columns based on adjacent criteria

★ 25 minute read

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

Formula

=SUMPRODUCT(--(array1=Criteria),array2)

Explanation

To sum up columns based on adjacent criteria, a formula based on the “SUMPRODUCT” function can be used.

Example 1.

 

 

Figure 1. Sum columns based on adjacent criteria

In figure 1 above, the formula in cell I6 is:

=SUMPRODUCT(--($B6:$F6=I$5),$C6:$G6)

The formula in cell I6 we can split as below:

=SUMPRODUCT{1,0,1,0,1} {1,"Y",1,"Y",1,})

Since SUMPRODUCT is specific to overlook errors that arise from multiplying text values, the final array then looks thus:

{1,0,1,0,1}

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:

— $B6:$F6=I$5

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:

{1,0,1,0,1}

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar