asked on

Conditional Format Formula

Hi,

Attached is a sample file with a dummy data and the result as I need it.

I'm looking for a formula to highlight Green the cells when an ORDER (column "A") has ALL it`s items whit the STATUS "READY" (Column "D").

Could you help ?

Regards,
Roberto.
SAMPLE-FILE.xls

etech0

Put this formula into another column:
=COUNTIFS(A:A,A4,D:D,"PRODUCTION")

Then use conditional formatting to highlight any rows where that columns value is zero.

Do you know how to do that?

Shanan212

=COUNTIF($A$4:$A$33,$A4)=COUNTIFS($D$4:$D$33,"READY",$A$4:$A$33,$A4)

Select cell A4 and set conditional formatting with formula using the above
Then copy the cell A4's formats to rest of the cells (on A column)
See attached for example
SAMPLE-FILE.xls

barry houdini

Perhaps turn that round a little - you can do as Shannan212 suggests but with this shorter formula

=COUNTIFS(A:A,A4,D:D,"<>ready")=0

regards, barry

Pabilio

ASKER

Hi,

I'm sorry that I did not give you some important details:

- We are using Excel 2003

- The CF should be for all Column "A" due that data comes from a Sql Database and it change a lot.

Due to this two conditions I can not use COUNTIFS or SUMIFS and (I believe) neither SUMPRODUCT

Any ideas ?

Thank you for your time.
Regards,
Roberto.

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pabilio

ASKER

Thank you very much Barry...

I restricted the formula to 10,000 Rows...The formula works perfectly and it does not slow down the sheet calculations.

If is ok with you, I'll give the chance to the other experts in case they have a different solution.

Regards,
Roberto.

barry houdini

No problem

There are several similar ways I know of, e.g. this version with MATCH will also work

=ISNA(MATCH(1,(A$1:A$10000=A1)*(D$1:D$10000<>"ready"),0))

regards, barry

Pabilio

ASKER

Thank you very much Barry.
I wish you have a great and happy new year.
Regards,
Roberto.