Explanation
Normally, SUMIFS is used with data in a vertical arrangement, but it can also be used in cases where data is arranged horizontally. The trick is to make sure the sum_range and criteria_range are the same dimensions. In the example shown, the formula in cell I5, copied down the column is:
=SUMIFS(B5:G5,$B$4:$G$4,"red")
Notice the criteria_range, B4:G4 is locked as an absolute reference to prevent changes as the formula is copied.
Totals for each color
By carefully using a combination of absolute and mixed references, you can calculate totals for each color in a summary table. Notice in the example below, we are now picking up the cell references, I4, J4, and K4 to use directly as criteria:
The formula below in cell I5, copied down and across the table is:
=SUMIFS($B5:$G5,$B$4:$G$4,I$4)
Notice references are set up carefully so that the formula can be copied across and down:
- The sum_range, $B5:$G5, is a mixed reference with columns locked
- The criteria_range, B$4:$G$4 is absolute and fully locked as before
- The criteria is a mixed reference, I$4, with the row locked
Related formulas
Sum entire column
Sum matching columns
Related functions
SUMIFS Function
The Excel SUMIFS function returns the sum of cells that meet multiple conditions, referred to as criteria. To define criteria, SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.
