+ Reply to Thread
Results 1 to 17 of 17

Sum cells with specific cell text and colour

  1. #1
    Registered User
    Join Date
    08-30-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    57

    Sum cells with specific cell text and colour

    I am trying to sum cells with specific text and cell colour, I know how to use the colour function to sum cells by colour but how do I combine the both to get the desired result.

    I have included a workbook showing what I am trying to achieve, I want to sum the cells that are green and say MED, which total 14:00hrs

    Thanks for any help or advice.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-29-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    255

    Re: Sum cells with specific cell text and colour

    How about =SUMIFS(B:B,A:A,"MED")
    Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum cells with specific cell text and colour

    what colour function? there is no sum by colour in excel 2003,
    ah i see a udf in your workbook to do that
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    01-21-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    227

    Re: Sum cells with specific cell text and colour

    one alternative is to have another column corresponding to colors and add the criteria in sumifs.

    =SUMIFS(B2:B7,A2:A7,"MED",C2:C7,"green")


    have a column C where it says green when the cells in A column are MED and Green.
    If I've been of help, plz add reputation.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum cells with specific cell text and colour

    There is no native (regular) function in excel 2003 that will count by color or by font or by any other formatting.

    If the color is applied through a specific rule, you could probably use that same rule to do the sum for you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    08-30-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    57

    Re: Sum cells with specific cell text and colour

    Thanks for the help so far but I have not been able to get the sumif formula
    Please Login or Register  to view this content.
    to work.

    Any one else have any ideas?

    When the colour of the cell changes it is done manually as not all MED meet the criteria to be coloured green, and this process has to be done manually as it is unknown till the day if this cell is required to be coloured green.

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum cells with specific cell text and colour

    what version of excel do you have?

  8. #8
    Registered User
    Join Date
    08-30-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    57

    Re: Sum cells with specific cell text and colour

    This is being done in 2010.

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Sum cells with specific cell text and colour

    Please find the attached sheet and see if this is what you are trying to achieve?
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum cells with specific cell text and colour

    if you are using this...
    =SUMIFS(B2:B7,A2:A7,"MED",C2:C7,"green")
    to find the actual color green, then no it wont work, because it is looking for the text "green". Perhaps, instead of using color formatting, you could use an extra (helper) column, and put an X where you would have colored green? That way, your suggested SUMIFS will work

  11. #11
    Forum Contributor
    Join Date
    01-21-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    227

    Re: Sum cells with specific cell text and colour

    Quote Originally Posted by Macfool View Post
    Thanks for the help so far but I have not been able to get the sumif formula
    Please Login or Register  to view this content.
    to work.

    Any one else have any ideas?

    When the colour of the cell changes it is done manually as not all MED meet the criteria to be coloured green, and this process has to be done manually as it is unknown till the day if this cell is required to be coloured green.
    it will work when u have an extra column C

    "have a column C where it says green when the cells in A column are MED and Green."

  12. #12
    Registered User
    Join Date
    08-30-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    57

    Re: Sum cells with specific cell text and colour

    sktneer

    Thanks for your help, can you tell me what formula I should use on this, I saw the UDF you have in the module but don't know what formula to use to test your solution, or am I missing the obvious.

    Thanks

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum cells with specific cell text and colour

    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

  14. #14
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Sum cells with specific cell text and colour

    I have used the formula in D2 and custom formatted the formula cell with h:mm.

    In D2
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    08-30-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    57

    Re: Sum cells with specific cell text and colour

    Thank you very much sktneer, that works perfect for my project, and thank you to everybody else who offered a solution.

    many thanks

  16. #16
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum cells with specific cell text and colour

    and thanks for updating your profile

  17. #17
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Sum cells with specific cell text and colour

    You're welcome. Glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 11-19-2013, 02:13 PM
  2. Edit Cell Colour if other cell displays specific text
    By macky18 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2013, 07:46 AM
  3. [SOLVED] Cells to change colour depending on specific text
    By Fran Habbitts in forum Excel General
    Replies: 4
    Last Post: 12-11-2012, 10:50 AM
  4. [SOLVED] Macro to change text colour of adjacent cell to the same text colour as target cell
    By kayoke in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-28-2012, 04:18 AM
  5. Getting range of cells with specific text colour, then using in a COUNTIF formulae
    By cheekyflash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2006, 08:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1