Count items in a column based on multiple criterias

[cgonzo1970]

Property Approved installed
6284 YES YES
6293 YES YES
6295 no YES
6307 no YES
YES no
6307 no YES
6320 no YES
6346 no YES

Hi on the first line, i have a property number of an equipment, I need to count the number of equipment installed and approved, not considering the duplicates property numbers or blank spaces.
I calculated the number of equipments =SUM(IF(FREQUENCY(A2:A9,A2:A9)>0,1))

i also calculated the number on occurrences where both approved and installed is yes =COUNTIFS(C2:C9,"YES",B2:B9,"YES")


but i need to combine them somehow to determine how many of the equipments were installed in the approved sites

help for this newbie will be appreciate.

[boopathiraja]

Hi, Welcome to the forum
Try with the below formula

Please Login or Register  to view this content.

[cbatrody]

@Boopathi,

=INT(SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)*(B2:B9="Yes")*(C2:C9="Yes"))) doesn't work when there is a blank cell in between in column A.


See the updated one below (in cell D2):

=SUMPRODUCT(SUM(1/COUNTIF(A2:A9,A2:A9))*(B2:B9="Yes")*(C2:C9="Yes"))