Hi Jennifer,

Excel can't sum by colour very well. In Excel 2007, you could use the autofilter to filter the column by colour and have a subtotal. The subtotal will give you the sum of only the cells that are visible.

If you don't want to use the autofilter then you would have to write some VBA code to create your own function to sum by colour. Even then, the VBA function would have issues because it wouldn't automatically recalculate as soon as the colour of a cell is changed, so it's not an attractive solution in my opinion.

Instead of using colours, a better idea would be to use an adjacent column and to use it to 'flag' which cells you want to sum. For example, suppose you want to sum some of the numbers in A2:A11. In column B, you could put a 1 in B3 and B5 and then use this formula to get the sum:

=SUMIF(B2:B11,1,A2:A11)

This will only sum the numbers which have a 1 on the same row in column B (ie A3 and A5). As you change the 1 flags in column B, the SUMIF formula will automatically calculate the revised total.