Check the condition and return the value as "1"

[Excelrunner_]

IN my Excel i have two conditions to fullfull if either one is true then return with the answer in the cell with the value "1"

Condition one & Condition Two.

If the text in the cell of the Row 1 is red or If there is a cell with Yellow highlight then return the value with "1" Else "0"

Thank you. Looking forward for the answer

[Excelrunner_]

Attached the file

[Glenn Kennedy]

In your real sheet, are the text colours and fills changed manually, or (using a rule) with conditional formatting? If it's done manually, is there any rule that you apply? Otherwise this is not easy.

[Excelrunner_]

Yes it has condition, I have done the coloring and the nightlights here manually.

If the Column I has Solution Design then consider column J, K L,M, N should be in the future from the current date Else the Row has "Error"
If the Column I has Decision pending then Consider Column J in the past and the rest K,L,M,N should be in the future else the row has "Error"

Thank you in advance for the reply.

[Glenn Kennedy]

OK. Can you repost your sample sheet, showing what you expect to see... and where you expect to see it. I follow your basic requirement (I think!!)

[Excelrunner_]

Hi Glen,

Thank you for the reply. Here is the file and the result that i would like to see at the end of the Column.
Please have a look at the file attached.

[bebo021999]

Try in AM2 then drag down:

Please Login or Register  to view this content.

[Excelrunner_]

Hi Bebo,

Have tried but returns with formula error. May be some parenthesis is missing !!!?

[bebo021999]

It works for me

[Excelrunner_]

Hi Bebo, Now i see it works in your file. Only difference is ; and ,

But the row 5 should return with 1 because there is no error in it. But it returns with the result Error !!!!!

[bebo021999]

K5 is 01-May-2020 in the past then error.

[Excelrunner_]

THank you so much for the formula it works Appreciated

[Excelrunner_]

Greetings i have one more additional feature together with this formula to enrich.

How to add if the further cells like R2, AB2, AC2, ACD2 are empty or value 0 Return it as Error ?

Thank you in advance.

[Excelrunner_]

=IF(OR(AND(I2="Solution Design",INDEX(J2:N2 > TODAY(),)),AND(I2="Decision pending",J2 < TODAY(),INDEX(K2:N2 > TODAY(),))),1,"Error"

How to add if the further cells like R2, AB2, AC2, ACD2 are empty or value 0 Return it as Error ?

Thank you in advance.

This applies for both Solution Design & DEcision pending.

[bebo021999]

Try:

=IF(OR(R2=0,AB2=0,AC2=0,AD2=0),"Error", "YOUR FORMULA")