Results 1 to 7 of 7

Thread: SUM number values according to days of week and highlight red If exceeds ?

  1. #1

    SUM number values according to days of week and highlight red If exceeds ?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I have these two columns :

    (F2 to L2) : 1. (Mon) 2. (Tue) 3. (Wed) 5. (Thu) 6. (Fri) 7. (Sat) 8. (Sun)

    (F4 to L4) : .11-13 .6-14 .6-13 .7-15 .7-15 .7-15 .7-15


    Question : IF SUM of number values exceeds =56 from Monday to Sunday, I want to highlight all number cells ( those which are SUM) with RED, or optional a Pop-up window like "You exceeded value ! ". Is It possible ?

    thanks for your help !

    Regards, Luka

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,271
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Sure thing. You need a conditional formatting rule.

    Not sure of your version of Excel, but if you're 2007 or higher then:
    • Select cells F4:L4 (start from F4)
    • Home Tab --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format --> Use the following formula
    • =SUM($F$4:$L$4)>56
    • Click "Format" --> Font --> Color --> Red
    • Click OK --> OK


    At this point it should turn red every time the aggregate in the cells exceeds 56

    Hope this helps,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Yes, I did that before posting this thread, but the problem is that when I select all cells and do as you instructed, It doesn't highlight all cells, but only 6 of them (If formula is TRUE or FALSE), and 1 of them only If formula meets desired value. I have to do this in my monthly worksheet for each week, and for around 70 rows, which is quite a long-time mouse-clicking If done one cell-by-one cell !

    I attached sample worksheet and you can see it yourself !

    P.S.: formula is in B3 cell, conditional formatting should apply from B2-H2 !


    Maybe I don't know how to select properly all cells, but you will see in Edit Rule that everything is as It should be !


    Thanks for respond,

    Regards, Luka
    Attached Files Attached Files

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,463
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Does the attached help ?
    The formula for the CF is
    Code:
    =SUMPRODUCT(($A$1:$AE$1-WEEKDAY($A$1:$AE$1,3)=A$1-WEEKDAY(A$1,3))*$A$2:$AE$2)>=56
    Sample-Hours-1.xls

  5. #5
    Napoleon would say : Veni-Vidi-Vici !


    It works great, just tell me this :

    1. How did you manage to work It for all cells, did you select cells in some special way ?

    2. What was wrong with my formula ?

    3. I noticed that you didn't insert your formula in any cell - just in rule of CF, is that right ?

  6. #6
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,463
    Articles
    0
    Excel Version
    2010 on Xubuntu
    1. I don't understand the question
    2. The best way to examine what happens in CF is to copy your formula in an empty cell in col B and drag right. There where it gives TRUE and should return FALSE , use the Evaluate formula tool to see what goes wrong
    3. Errrr, AFAIK that's the only way to implement CF ?

  7. #7
    OK, don't worry about my questions, I'm Excell beginner. Could you explain your code in one or two words (how It works), I don't understand It quite good.

    And thank you, I have tried It in my workbook and It just works perfectly

Posting Permissions

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