Count specific cells with multiple criteria duplicated content must only be counted once

[christhomas99]

Hi have a tracker spreadsheet monitoring the progress of building works. The building works have several statuses that they may go though this is identified by the Property Status column. There are 3 different types of status Statuses beginning with IP (In Progress), CPL (Complete) and HLD (on Hold) see the attached LISTS on the sample spreadsheet.

What I want to do is count how many properties are IP how many CPL and how many HLD. Normally this would not be a problem however in some instances a property may have multiple works orders assigned to it the sample spreadsheet shows one of these properties (highlighted in orange).

The property with 2 works orders 1 order is still in progress and 1 order is complete there is however only 1 property so this can only be counted once the work for that property is still outstanding so need to be counted as 1 property that still has work to complete

The second property that has 2 works orders again is only 1 property and must only be counted as 1 property that still has works orders outstanding.
I’m happy to add additional columns if required however I cannot work out a way to break these down can any of you gurus please help.

[JeteMc]

Perhaps the following will help.
Column AR displays the status type for each row and is populated using: =LEFT(P18,SEARCH(" - ",P18)-1)
Column AS displays the count of CPL and is populated using: =IF(COUNTIFS(E$18:E18,E18)=1,(COUNTIFS(E$18:E$33,E18,AR$18:AR$33,AS$17)=COUNTIFS(E$18:E$33,E18))+0,0)
Column AT displays the count of IP and is populated using: =IF(COUNTIFS(E$18:E18,E18)=1,(COUNTIFS(E$18:E$33,E18,AR$18:AR$33,AT$17)>0)+0,0)
Column AU displays the count of HLD and is populated using: =IF(COUNTIFS(E$18:E18,E18)=1,(COUNTIFS(E$18:E$33,E18,AR$18:AR$33,AU$17)=COUNTIFS(E$18:E$33,E18))+0,0)
AR16:AU16 display the totals of each status.
Let us know if you have any questions.

[christhomas99]

Hi JeteMc, First of all brilliant I think this does the trick, I was seriously overthinking the problem from the way I was tackling it for sure. I really appreciate the time you have taken to help me out on this I was getting to the point were I was thinking it wasn't possible. Thanks so much mate.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.