Auto sum - excel

[Jennifer Pannell]

Newbie here with question. Excel 2007, have 1 column with 10 rows of numbers. I want an auto sum of the column, but only total the ones I have highlighted yellow or any color. Example 1 column, 10 rows, each row has the number 10. Only 7 of the 10 are highlighted a color so I want the auto sum to come out total 70, not auto sum 100.

[Colin Legg]

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.

[Jennifer Pannell]

Great ideal about the adjacent column and to use it to 'flag' the highlighted ones. That will solve my issue - THANKS!!!