+ Reply to Thread
Results 1 to 15 of 15

Sumif by color

  1. #1
    Forum Contributor
    Join Date
    02-19-2013
    Location
    Kuwait
    MS-Off Ver
    Excel 2013
    Posts
    108

    Sumif by color

    hello guys,

    Is it possible to have a forumlae based on cell color, background being green,

    I tried searching online. they always give confusing vb editor codes.

    Thats why i need a simple forumlae with color being green

    Best Rgds
    AT

  2. #2
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Sumif by color

    Hi Brand,

    You can have it but for that you must need one helper column (assuming you are formatting cell based on some value and then you want other cell to change as per color formatting).

    Regards,
    Anil
    Thanks,
    Anil Dhawan


    Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.

    Don't stop when you are tired. STOP when you are done!

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,263

    Re: Sumif by color

    No. Colour is not data. That's why people have developed VBA routines.

    You can filter by colour and SUBTOTAL will exclude hidden row so you could maybe use that as a workaround.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Sumif by color

    No formula will do that. You can use Autofilter and filter by color and then use SUBTOTAL(109.

    If that's not good enough then this UDF will do it for you. Once you pasted the code it will be just like a new function in your workbook.
    http://www.ozgrid.com/VBA/Sum.htm

    Just post again if you have trouble with UDF and you will get guidance.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Sumif by color

    How the cells are formatted?

    Manually or Using Conditional Formatting?

    If the formatting is not manual then surely we can Sum() the data


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  6. #6
    Registered User
    Join Date
    04-15-2015
    Location
    Vietnam
    MS-Off Ver
    2013
    Posts
    3

    Re: Sumif by color

    Is there any UDF to add value to cell based on its color? not sum but to add the value to cell.

    Thank you,
    TA

  7. #7
    Forum Contributor
    Join Date
    02-19-2013
    Location
    Kuwait
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: Sumif by color

    Hi jacc,

    I want to use sum by color function, So I am using attached function.
    When i run it, it shows an error

    Please chk n let me know

    Thanks
    AT

    I am facing attached error
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-19-2013
    Location
    Kuwait
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: Sumif by color

    Hi sixth sense,

    The formatting is done on manual basis, pls suggest ur solution

    Rgds
    AT

  9. #9
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Sumif by color

    @ Anh
    Please read the forum rules carefully. You can not post question on someone else's post


    Forum Rules

    Please read and adhere to these simple rules!

    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.

    Also, please continue with your open threads there only you will get answer for your query. . http://www.excelforum.com/excel-gene...und-color.html

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Sumif by color

    Quote Originally Posted by brandedadnan View Post
    The formatting is done on manual basis, pls suggest ur solution
    As described by other members you have to go for VBA approach only...

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Sumif by color

    You copied to far down, this part needs to be there as well:

    Please Login or Register  to view this content.
    or specifically the top row of it which is where you tell Excel that this is a function. The text preceeded by a ' is just comments made by the author, you can skip that.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,263

    Re: Sumif by color

    Or AutoFilter and SUBTOTAL as I said earlier.

  13. #13
    Forum Contributor
    Join Date
    02-19-2013
    Location
    Kuwait
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: Sumif by color

    Thanks Jacc, worked perfectly, I did not know u can add functions to excel as well.

    Great learning curve,
    Thanks all for ur efforts.
    Rgds
    AT

  14. #14
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Sumif by color


    ''''''''

  15. #15
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Sumif by color

    -----------

+ 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. Sumif on cell color
    By Bridge1969 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2014, 01:27 PM
  2. [SOLVED] SUMIF (cell color)
    By snowb46 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-21-2014, 07:31 PM
  3. SUMIF with color criteria
    By sash55511 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-27-2013, 04:17 PM
  4. SUMIF color=...
    By JChandler22 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2007, 03:32 PM
  5. SUMIF on Fill color
    By Dave H in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2005, 06:05 AM

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