Formula to use w conditional format, Message if 3 dates match/unmatch if range blank or bigger than

[AndyJR]

Hi, I'm sorry the title sound weird,

I need to use various formulas (or one long formula) with conditional formatting and this is the scenario.

I need to display different message if some condition match.

i have 3 dates and range that sometime have string sometime is blank

Date1 -> Cell G12
Date2 -> Cell P12
Date3 -> Cell C12
String -> I12:M12

I know that i need to use logical IF AND but i don't really know how to add the range when is blank or >0 (sorry)
This is what i need :
Note: i need to display my message in cell I14

If I12:M12 >0 and DATE1 no match But DATE2+DATE3 match "mymessage1"
If I12:M12 is blank and DATE1 no match But DATE2+DATE3 match "mymessage2"
If I12:M12 >0 and DATE1+DATE2 match But DATE3 not match "mymessage3"

This is the formula that i'm using and i need to replace:

=IF(AND(G12=P12,P12=C12),"message1","message2")

Thanks you so much

 

[AndyJR]

"bump"

 

[jtakw]

Hi,

I'm not entirely sure I understand your question, this is based on what I think you mean, and your description does not cover all scenarios:

=IF(COUNTA(I12:M12)=0,"mymessage2",IF(P12=C12,"mymessage1",IF(G12=P12,"mymessage3","")))

 

[MarcelBeug]

Assuming I12:M12 must all be >0 OR all blank, the following array formula (confirm with Ctrl+Shift+Enter) should do the trick. Note it includes stuations that are undefined in your specifications.

=IF(AND(I12:M12>0),IF(AND(G12<>P12,P12=C12),"mymessage1",IF(AND(G12=P12,P12<>C12),"mymessage3","undefined")),IF(AND(I12:M12="",G12=P12,P12<>C12),"mymessage2","undefined"))

Edit: you can adjust this in a regular formula by using COUNTA as in the formula by jtakw.

=IF(AND(COUNTA(I12:M12)=5),IF(AND(G12<>P12,P12=C12),"mymessage1",IF(AND(G12=P12,P12<>C12),"mymessage3","undefined")),IF(AND(COUNTA(I12:M12)=0,G12=P12,P12<>C12),"mymessage2","undefined"))

 

[AndyJR]

Hi MarcelBeug,

I'm going to try right now!


Thank you so much!!!!!!!!!!

 

[jtakw]

Hi,

I have a revised version that is probably a little more accurate than the one I posted in post #3:

=IF(COUNTA(I12:M12)>0,IF(AND(G12<>P12,P12=C12),"mymessage1",IF(AND(P12<>C12,G12=P12),"mymessage3","")),IF(AND(G12<>P12,P12=C12),"mymessage2",""))

 

[AndyJR]

Thank you MarcelBueg and Jtakw

Work like a charm...


Thank you, appreciated a lot

 

[AndyJR]

Hi jtakw,

new formula version, it worked too!!

thank you !

 

[jtakw]

You're welcome, glad to help.