CODEWITHSUNDEEP
excel-formula
Kelvin C
Kelvin C

Reputation: 121

Excel Formula - If any cells within a range contain specific value, then

I don't quite know how to word this one, but unable to write the correct formula.

I've written =IF((AND((COUNTIF(B:B,B31)),C31 = "Comp")), "yes", "no") which is clearly wrong.

Below is an image of the document:

enter image description here

What I'm trying to do is create a formula that will look at all the Unit Numbers in Column B, and check if the work orders have been completed in column C (With COMP indicating completed, and blank indicating not) and then say either yes or no in column E. The problem is that I need column E to say no if any row with the same unit number does not have all of its work orders completed.

So unit 101 would all say YES. Unit 102 would say NO, because two Work Orders have not been completed, etc.

Any help would be greatly appreciated.

Thank you

Upvotes: 1

Views: 2776

Answers (1)

P.b
P.b

Reputation: 11425

=COUNTIF(B:B,B2)=COUNTIFS(B:B,B2,C:C,"COMP") this would return TRUE for all completed and FALSE if any still not completed. If you want it to actual say Yes or No, wrap it in IF().

Upvotes: 1

Related Questions