+ Reply to Thread
Results 1 to 4 of 4

Trying to get SumByColor working

  1. #1
    Registered User
    Join Date
    04-10-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Question Trying to get SumByColor working

    Hi all, I was trying to add in a function to add a column based on row colour and came across the "sumbycolor" macro. I've added it to the attached sheet but for some reason it doesn't calculate properly for all columns (see for example Client 2 & Client 4). Have I gone wrong in the formula somewhere?

    (Btw I was originally asking in the Formulas thread about the SUM function but then found out VBA/Macro may be the way to go, so sorry for any duplication of posting!)
    Attached Files Attached Files

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Trying to get SumByColor working

    You should be using Double not Long as your numbers are not whole numbers. You also need to make the function volatile, and it's better not to pass the cell the function is in as an argument:
    Please Login or Register  to view this content.
    Of course it's also far better not to use colours as data.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Trying to get SumByColor working

    Your reference cells in row 3 to pick up the colorindex are conditionally formatted, which is not picked up by colorindex. And CF will change the color without triggering a re-calc, so that self-referential technique is just a bad idea.

    If you are using CF to find the cells to sum, then you should use a formula that replicates the CF rule. If you are manually coloring the cells, then instead of referencing a cell that uses CF, use a cell that is also manually formatted for the color of interest.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Registered User
    Join Date
    04-10-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Trying to get SumByColor working

    Thanks guys, the conditional formatting was just intended to be helpful for row 3 to automatically show me if any client had a negative balance in the client account. The balances as at the beginning of each month are coloured manually.

    I tried copying & pasting your formula over the macro Romperstomper but still got the same result. Given that I don't really understand macros yet I replaced the formulae with:

    =SUMIF($F$6:$F$10001,"",G$6:G$100001)

    which was provided by TNS of these forums and works a treat. I guess that keeps it simple too and I can keep to a non-macro workbook. I should probably get a better understanding of macros before coming back to them. I appreciate your help though!

+ 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. [SOLVED] Countif + SumByColor
    By sordid in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-11-2014, 05:55 AM
  2. [SOLVED] SumByColor(Rng)
    By JimMay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2006, 07:45 AM
  3. VBA defined function problem sumbycolor
    By Brian Matlack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2006, 08:17 AM
  4. how to use sumbycolor fuction?
    By Ghauri in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-29-2005, 05:10 PM
  5. [SOLVED] SumByColor Macro...
    By Murph in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2005, 07:05 PM

Tags for this Thread

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