which returns the total of items in “Red” columns for each row. 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: 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
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
