Conditional formula , maybe simple but I am stuck.

[claustro]

Hi ,
I need help with conditional formulas.
I have an Excel sheet for a Hospital timesheet.
Columns are days row are duties.
The type of duties are :
Morning indicate by a M in column AR
Evening indicated by a P in column AR
Ready for Availability by a R in column AU
What I would like to achieve is if someone is assigned to an Availability ( R row ) duty on a day it becomes colored if I assign the same employee the next day morning (M row).
I am providing my file where I manually colored what I would like to achieve.
C53 (R in AU) colored because morning on D47 ( M in AR)
F57 ( R in AU ) colored because morning on G47 ( M in AR)

This is what I was thinking about but maybe I am missing something
=SUMPRODUCT((C4=D$4:D$57)*($AU4="R")*($AR4="M")*(C4<>""))>0
Thank you
Andrea

Orario V26 FORUM.xlsm

Shared with Dropbox

www.dropbox.com

 

[claustro]

up

 

[Peter_SSs]

up

Many of the helpers here choose not to download files from other sites or, due to security restrictions at their workplace, are unable to download such files.

You will generally get more potential helpers if you explain your problem clearly in words and, if needed, post a small (copyable) screen shot or two directly in your post. My signature block below has help regarding that - see the XL2BB link.

 

[claustro]

Thank you for your answer. English isn't my main language so maybe I am having some difficulty trying to explain myself at my best.
I made a screenshot describing what I would like to achieve
This is the formula I come about but it isn't working as intended
=MATR.SOMMA.PRODOTTO((C4=D$4:D$57)*((($AU4="R")+($AR4="M"))*(C4<>"")))>1

Thank you all

 

[claustro]

up

 

[claustro]

up