Populating cell depending on the value of a range

[ukbulldog001]

Hi,

I'm looking for a solution or a formula which could solve my problem.

[/URL][/IMG]
I want the "STATUS" column to be populated as per the value available in "COUNT" column which is always a numerical.
All the "STATUS" should state "PR" if any of the cells in range "COUNT" corresponding to the Sl.No. group is more then zero.
Table A should be turned into Table B.
Hope above image is easy to understand.

Any formula or a method would be helpful.
At present I'm using =IF(D3>0,"PR","") in A3 and then populate manually.

Thanks.

 

[TimFoley]

Can you clarify why A2 (B2=ABCD-01) is marked as PR while A8 (B8=UKL-01) is not?

 

[AlanY]

try this

=IF(OR(D3>0,AND(D3=0,B3=0)),"PR","")

 

[TimFoley]

Assuming that the PR marks are based on the main entry (ABCD) and apply to all sub entries (ABCD-01) then I would add an additional helper column in E with the formula: =IFERROR(LEFT(C3,FIND("-",C3)-1),C3). Then in A enter the formula: =IF(OR(B3>=D3,AND(A2="PR",E2=E3)),"PR","")

 

[ukbulldog001]

Can you clarify why A2 (B2=ABCD-01) is marked as PR while A8 (B8=UKL-01) is not?

The NUMBER column is a group like all the ABCD, ABCD-01 and ABCD-02 comes under ABCD, and so for IJKL AND IJKL-01 comes under IJKL. i.e., for Sl.No. 1 and 3 respectively.
A2, A3 and A4 (ie, ABCD, ABCD-01 and ABCD-02) should be "PR" if any respective cells contain values more than 0 (ie, D2, D3 and D4 containing value more than zero).

 

[TimFoley]

Oh, so if *ANY* of the cells D2, D3, D4, then all members of ABCD should be marked as PR.

Then I would add an F column with the formula =sumif(E:E,E2,D:D) and change A to =IF(F2>0,"PR","")

 

[ukbulldog001]

try this

=IF(OR(D3>0,AND(D3=0,B3=0)),"PR","")

The resulting table should look like Table B. Does not work. thanks.

 

[ukbulldog001]

Awesome, that worked like a charm, Thanks a lot.