Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Sum based on colour?

    I want to sum a selection of cells based on the colour of the cell. Is that possible?

    thanks

    Alan

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Only with code. You would be better off using data to colour the cells (using conditional formatting) if you can rather than trying to use colours as data. You can create functions in VBA to do it but they are not perfect.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Humm - not quite that easy. I have manually set up certain cells with a colour to highlight things. It would have been nice if there was an "IF" type statement such that Could do an "IF(colour=blue, sum....).

    But if it's too hard, it's easier to just do it manually.

    thanks

    Alan

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    If you are looking for ANY colour, as opposed to no colour, then the CELL() function can be used. Read about it here: http://office.microsoft.com/en-gb/ex...010062392.aspx

    If you are looking for a PARTICULAR colour then it is still possible but you will have to use VBA. If you aren't too familiar with VBA then I'd suggest a User-Defined Function such as this: http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm : you can then use the function COLORFUNCTION() in normal formulas to achieve what you want.

  5. The Following User Says Thank You to MartinM For This Useful Post:

    alan sh (2012-12-18)

  6. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    That macro works EXACTLY as I want. Brilliant. Many thanks

    Alan

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just remember that those formulas will not automatically update if you change the colour of a cell (which is what I meant about them not being foolproof). There's also a good page by Chip Pearson here.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    That's OK - if the colour changes, I'll do a manual recalc.

    Cheers

    Alan

  9. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    If/when you recalculate, F9 won't work - you have to do Ctrl+Alt+F9​.

  10. The Following User Says Thank You to MartinM For This Useful Post:

    alan sh (2012-12-18)

Posting Permissions

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