# Thread: Calculate Cells of a color (2007 / 12.045....)

1. ## Calculate Cells of a color (2007 / 12.045....)

Is there a way to perform calculations on only cells of a certain color?

Or on cells that are NOT a certain color? I know you can filter and subtotal but my sheet has the colored cells in different rows on many columns.

Thanks

2. ## Re: Add Cells of a certain color (2007 / 12.045....)

Evening

I do not know if any of this works in 2007 but there are some good examples on Chris Pearsons site, here, ColourFunctions

Cheers

Steve

3. ## Re: Add Cells of a certain color (2007 / 12.045....)

Thanks Steve. that is a bit beyond me, though. I'm not an advanced user.

I was hoping there was some kind of simple if statement I could past in like if cell is not xcolor then add it in . LOL oh well.

thanks again, Heather

4. ## Re: Add Cells of a certain color (2007 / 12.045....)

Excel doesn't have built-in support for counting or adding cells according to color. You can use the VBA functions written by Chip Pearson mentioned in Steve Hocking's reply, or you can install ASAP Utilities. This add-in is free for personal use and provides worksheet functions to count or add cells by font color or fill color.

5. ## Re: Calculate Cells of a color (2007 / 12.045....)

A formula way in use XLM macro functions with helper column

1] Assuming you have range of datas in Cell A1:A10, with some yellow coloured cells

2] Select Cell B1 >> Insert >> Name >> Define

3] Defined workbook name : ColorCell

4] Refer to Box, enter formula :

=GET.CELL(38,A1)+NOW()*0

5] Cell B1, enter the formula, and copied down to D10 :

=ColorCell

6] Cell B11, enter the formula in sum cells with yellow colour :

=SUMIF(B1:B10,6,A1:A10)

Regards
Bosco

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•