Count the data in column on sheet 2 from results in column on sheet 1 after matching data in corresponding column

[DanJones]

Sheet1

Sheet 2

Hi, I am trying to do a calculation if column D in Sheet 1 = As Built, it takes the corresponding data in Column K and counts the occurrences of it in Column H on Sheet 2. I have tried all sorts of combinations of INDEX, COUNT, SUMPRODUCT etc. the closest I am getting is =SUMPRODUCT(--ISNUMBER(MATCH('CBT OR Chamber'!K:K,'SY-property-statuses'!H:H,0))) but that is not taking into account the column D in Sheet 1 = As Built. Please help, new to this forum and it's the first time I have been this stuck, so sorry if it is a stupid repeated question!

 

[DanJones]

=COUNT(FILTER(ROW('SY-property-statuses'!H2:H1000),ISNUMBER(MATCH('SY-property-statuses'!H2:H1000,FILTER('CBT OR Pole'!K2:K1000,'CBT OR Pole'!D2:D1000=A4),0)),""))

Brilliant, thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.