Total the Red Cells
January 10, 2022 - by Bill Jelen
Problem: I’ve marked several cells in red. I need to 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.
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.
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. Watch this YouTube video: https://mrx.cl/sumredmacro for details.
This article is an excerpt from Power Excel With MrExcel