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

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

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 ?

Regards, Luka

2. 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,

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

4. 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. 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. 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. 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
•