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

[Lukael]

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

[etaf]

can you load a sample spreadsheet

Not sure if you just have a number in cells F4 to L4 or if you have the text as shown

if a number
you should be able to use conditional formatting
=sum($F$4:F4)>56

see attached

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>
F4 to L4

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=sum($F$4:F4)>56

Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

[Lukael]

Here is a sample spreadsheet,

I hope now you will understand.
sample.png

[etaf]

not a spreadsheet , thats a picture - a spreadsheet would help

no sorry, i don t understand - i see the hours worked are a number in row 5 - so just change to use row 5

and you can change the apply to row 1 if you like

dont know the criteria you are using for the red and yellow on the days of week

a fuller explanation would help me

[Lukael]

Ok, here is spreadsheet.

other colours are not important. This is a sheet of working hours for employees. All I need is to calculate these hours from Monday to Sunday - if they exceed 56 then this must be highlighted or even best, a popup window "Hours have exceeded max. week limit !"

[Lukael]

found a solution for me:

=SUMPRODUCT(($A$1:$AE$1-WEEKDAY($A$1:$AE$1,3)=A$1-WEEKDAY(A$1,3))*$A$2:$AE$2)>56


Anyway, thanks for all your help !!

[etaf]

glad you found a solution and thanks for posting here - very useful to know