Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Attached Files Attached Files

Posting Permissions

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