Conditional sum based on number color

I have a column for which I want to sum the numbers. The numbers start out as all red. As I change the numbers to black, then I want that number to be added to the total. At various times I will have a mixture of red and black numbers. I only want the black numbers to total.

This tutorial should give you an idea or two on how to accomplish your end result.
http://www.datapigtechnologies.com/f...rtonColor.html

Alan

Hi jimisom

I've used this Function with success http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

Hi jimisom

you could use a UDF
red= 3 and black=1

Please Login or Register  to view this content.

On this subject I would just restate the point made in the OzGrid reference, namely:

Originally Posted by OzGrid

Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9

Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.

In short, this approach is fundamentally flawed.
You are in essence:

a) building into your model the potential for calculation errors.

b) using UDF rather than native functions - generally slower

c) introducing calculation overhead via Application.Volatile (worse given point 2)

Given the above facts differentiation / calculation based on manual formatting should be avoided at all times.

Originally Posted by jimisom

The numbers start out as all red. As I change the numbers to black, then I want that number to be added to the total.

You should be using adjacent to cells to denote a marker for inclusion/exclusion, at which point use the markers to:

a) act as source for Conditional Formatting rules - to determine fill colour of number cells

b) act as basis for SUM calculations (ie SUMIF)

Point well taken DO.