Home » Posts

Sum columns based on adjacent criteria

To sum values in columns based on criteria in an adjacent column, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in K5 is: =SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5) As the formula is copied across and down, it returns a sum of values associated with "A" and "B" in the table to the left.

April 14, 2025 · 1 min · 68 words · Travis Phillips

$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.

Excel formula: Sum every nth column

Only the values in the second array that correspond to 1s in the first array survive this operation.

This represents the numbers that correspond to “A'” in row 5.

Excel SUMPRODUCT function