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

#### Lukael

##### New member
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

#### Ken Puls

Staff member
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,

#### Lukael

##### New member
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

#### Attachments

• Sample-Hours.xls
20.5 KB · Views: 15

#### Pecoflyer

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``
View attachment Sample-Hours-1.xls

#### Lukael

##### New member
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 ?