Excel: Total the Red Cells

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I've marked several cells in red. I need to total the red cells.

  1. Total the red cells.

    Strategy: Use the new filter by color to show only the red cells. Right-click on a red cell and choose Filter, Filter by Cell Color.

  2. Choose to filter by cell color.

    Only the red cells will be shown. After applying the filter, go to the first visible blank cell below the data and press the AutoSum button or Alt+Equals. When applied to a filtered dataset, the AutoSum button switches from the SUM function to the SUBTOTAL function. This function will sum only the visible cells, providing a sum of the red cells.

  3. AutoSum uses SUBTOTAL now.

    Additional Details: This feature will work even if the red has been applied by conditional formatting.

    Gotcha: When you clear the filter to show all cells, the formula will include the non-red cells. If you need a formula to add the red cells while displaying the other cells, you would have to use a User Defined Function in the Excel VBA language.