CODEWITHSUNDEEP
excelexcel-formula
Hien Le
Hien Le

Reputation: 47

Reference range base on the value of the cell

I have this workbook: enter image description here

I will input into column A the status of the job like "X" and "WD". There are multiple jobs on the column B but if one job have X then all of the job is "Bad" like what you see on the right. The only time that the job is good is when there are nothing on the column A like the job "JKL".

I tried =IF(COUNTA(A2:A11)=0,"Good","Bad"). It works but the problem is that I need to replicate this process for 200 jobs which mean I need to specify the range every time which is really easy to mess up the range if I insert another row.

If there is a way to define the range base on the value of the cell. For example, if the job is "ABC" then excel will choose range A2 to A11, if the job is "JKL" then excel will choose range A12 to 21

Thank you

Upvotes: 0

Views: 73

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

Try this formula in E2:

=IF(COUNTIFS($B:$B,D2,$A:$A,"X"=0,"Good","Bad")

If you have the unique job names placed in column D, you can now simply copy down the formula.

In order to get the unique job names, either use a pivot table or the advanced filter.

Upvotes: 1

Related Questions