This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Total the Red Cells
Problem: I’ve marked several cells in red. I need to total the red cells.
Figure 279 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.
Figure 280 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.
Figure 281 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.