conditional formatting

[smulder1]

Hi - I have a large dataset where i'm looking at applications going through various stages of testing and the machine name on which they live. What I want to do is to create a rule that highlights the machine in Green when everything is complete. For example

Machine Name---App Name---App Status
a --- app1 --- Waiting
a --- app2 --- Pending
a --- app3 --- Complete
a --- app4 --- Complete
b --- app3 --- Complete
b --- app5 --- Waiting
c --- app4 --- Complete
c --- app6 --- Pending
c --- app7 --- Pending
c --- app8 --- Pending

So, based on this, how could I get to a point where when all apps against a given machine name were complete, that machine was highlighted? Whilst any apps are anything other than the complete the machine and all the app rows associated should remain without formatting.

 

[Paul]

Assuming these are columns A / B / C. Use a conditional format for Column A.

=AND(ISBLANK(A1)=FALSE,COUNTIF(A:A,A1)=COUNTIFS(A:A,A1,C:C,"complete"))

 

[smulder1]

Thanks for that, but it's telling me that's too few arguments for the formula

 

[Paul]

Thanks for that, but it's telling me that's too few arguments for the formula

It works perfectly on mine. You should be able to copy and paste that right into the conditional format and set your formats and should work fine. Can you copy and paste what you typed in? There must be something missing.

Paul

 

[smulder1]

I got it working now, thank you. Just one more question, how would I adjust it to include a second criteria in that column C? So for example if I wanted to highlight anything that is "Completed" or "Pending"?

 

[Paul]

I got it working now, thank you. Just one more question, how would I adjust it to include a second criteria in that column C? So for example if I wanted to highlight anything that is "Completed" or "Pending"?

Not sure if i understand correctly but if you want the same thing but highlight the machine if it is complete or pending use

=AND(ISBLANK(A1)=FALSE,COUNTIF(A:A,A1)=COUNTIFS(A:A,A1,C:C,"complete")+COUNTIFS(A:A,A1,C:C,"pending"))

if you just want the items in column C to highlight if complete or pending, apply this formula to conditional formatting for column c

=OR(C1="complete",C1="pending")

Hope it helps.

Paul

 

[smulder1]

I can't get this to work. It doesn't seem to register that there are different number of apps for each machine, so one machine might need to consider 5 rows, others may only need to check one row, and the formula doesn't pick that up. I even added a new row and tried to test it, but no luck.

 

[Paul]

I can't get this to work. It doesn't seem to register that there are different number of apps for each machine, so one machine might need to consider 5 rows, others may only need to check one row, and the formula doesn't pick that up. I even added a new row and tried to test it, but no luck.

Do you have the "applies to" in the conditional format set to Sheet1!$A:$A. (replace sheet1 with whatever your sheet is named)

The COUNTIF(A:A,A1) will definitely pick up all instances of the machine name in that column. They need to be exactly the same so no extra spaces or commas, .....

Paul