Finding corresponding text

Question

I have a spread sheet which includes Town_name, Road_Name, Survey_status (Complete, WIP, Pending), i need to check the status of survey and put overall status against the town_name in another spread sheet.

Please check sheet

Here, if any town is under WIP (Work in Progress) then the overall status should be WIP, if all Done, then it should be mentioned as Done. There are about 1000 odd towns and it is becoming difficult to manually check and put the status.

Is there any formula that can help me out from this type of situation.

Answer 1

Use COUNTIFS:

=IF(COUNTIFS(Spreadsheet_1!A:A,A2,Spreadsheet_1!C:C,"WIP"),"WIP",IF(COUNTIFS(Spreadsheet_1!A:A,A2,Spreadsheet_1!C:C,"Pending"),"Pending",IF(COUNTIFS(Spreadsheet_1!A:A,A2,Spreadsheet_1!C:C,"Done"),"Complete","No Entry")))