+ Reply to Thread
Results 1 to 4 of 4

sum up n cells from 1 to n and color them if total isn't matching

  1. #1
    Registered User
    Join Date
    03-16-2017
    Location
    Germany
    MS-Off Ver
    365 ProPlus
    Posts
    9

    sum up n cells from 1 to n and color them if total isn't matching

    Hello,

    I'm trying to solve the following without VBA:

    Unbenannt.JPG

    The left side is the total of the right side.
    Now I'm trying to highlight the right cell group if the summation is not equal to the total on the left side.
    Actually that's not hard to achieve, but I want that it automatically detects his left side parent. If that would be kind of possible then I could set the formula for the whole column.

    My idea is:
    - get the parent cell (at D3 it would be B3, at D4 it should be B3)
    - subtract the cell value from the parent cell and put it at the parent row at e.g. F3
    - in the first case it should does the steps above 3 times; then if F3 is not zero D3, D4, D5 should highlight in red.

    BUT I don't know if it's even possible, because I couldn't find even the first step.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: sum up n cells from 1 to n and color them if total isn't matching

    Hi,

    in the Excel file attached I'm using two helper columns to activate conditional formats

    In E3

    =IF(A3,IFERROR(SUM(C3:INDEX(C4:C$100,MATCH(1,INDEX(--(B4:B$100<>""),),0)-1)),SUM(C3:C$100))<>A3,"")

    in F3


    =IF(A3,IFERROR(SUM(D3:INDEX(D4:D$100,MATCH(1,INDEX(--(B4:B$100<>""),),0)-1)),SUM(D3:D$100))<>A3,"")

    Conditional formatting rules:

    in C3, rule to be extedended below:

    =LOOKUP(2,(1/(E$3:E3<>"")),E$3:E3)

    In D3

    =LOOKUP(2,(1/(F$3:F3<>"")),F$3:F3)

    Regards
    Attached Files Attached Files
    Last edited by canapone; 03-17-2017 at 06:10 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    03-16-2017
    Location
    Germany
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: sum up n cells from 1 to n and color them if total isn't matching

    Hi,

    everything worked fine until I have just on item for the second part.

    Unbenannt.JPG

    With the last two columns I tried to display the difference, if there is any. But is there just on item it takes the number from the next main row. Is there a nice way to solve this too?
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,748

    Re: sum up n cells from 1 to n and color them if total isn't matching

    See attached:

    Helper in F

    =IF(A3<>"",MAX($F$2:F2)+1,F2)

    in E

    =IF(A3<>"",SUMIF(F:F,F3,C:C)=A3,"")

    CF based on test if E is FALSE

    =$E3=FALSE
    Attached Files Attached Files

+ 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. 3 choices to change other cells color if matching condition
    By clpickett3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2016, 12:51 PM
  2. color change for matching value cells
    By DanPop in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-18-2016, 04:44 PM
  3. Matching cells across sheets and returning color value based on the sheet
    By stefanapoli in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-24-2013, 07:28 PM
  4. Color cells if matching value exists
    By rainbowforest in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2011, 02:53 PM
  5. Replies: 4
    Last Post: 06-28-2011, 07:14 PM
  6. [SOLVED] Color background with Matching cells in row
    By Diggsy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-31-2006, 05:10 PM
  7. [SOLVED] I have cells with diffent color fonts and need to total based on .
    By KyaraB in forum Excel General
    Replies: 1
    Last Post: 04-20-2005, 01:06 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