Need formula - Urgent

[jeetusaini85]

Hello there,

Need your help to resolve my query. I need a formula for given below criteria:

I have two column, in 1st column i have some firm name and in 2nd there are status of the names like pending, received etc. Now the problem is, in 1st column firm names are in multiple rows with status. I need a formula in which :

If all the status of a firm is "Received" it should mark "Received" in front of a single name of the firm and if there is any other status with "Received" it should mark "Pending". Like:

Column A Column B Column C
Firm name Status New Status
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received Received
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -

ABC Pvt. Ltd. - Pending -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received Pending
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -


Thanks,

 

[jeetusaini85]

Hey Andrew,

Sorted out all the query myself with your guidance in formula, here is rectified formula as i need:

=IF(COUNTIF(B$2:B2,B2)=1,IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Received")=COUNTIF(B$2:B$23,B2),"Received",IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Pending"),"Pending","-")),"-")

Thanks a lot

 

[cmwcg]

=if(countif(a:a,"firm name")=countif(b:b,"received"),"received","pending")

 

[jeetusaini85]

Hey cmwcg,

Thanks for reply but there are lots of firm name which cannot write in formulas. Thanks for your contribution.

Thanks