[ms office - 2013] Excel query

[ajp1601]

I am trying to count duplicates in a column but only if they meet a criteria in another column, have managed to count the unique values using the following

 

{=SUM(IF(FREQUENCY(IF(F2:F150="Y",MATCH(A2:A150,A2:A150,0)),ROW(A2)-ROW(A2)+1),1))

 

I am sure there is probably an easier way to do the above and to count the duplicates but whatever I try fails miserably having OFSTED brain.

In the below example I want to count Fred 3 times and ignore the others.

 

 

Example

 

Fred 100 200 300 100 Y

Fred 200 200 300 100 Y

Fred 400 200 300 100 Y

George 100 200 300 100

Peter 200 200 300 100 Y

[ReadTheNetwork]

Not sure if you still need it but have you looked at countifs?

=COUNTIFS(f1:f5,"Y",B1:B6,100, a1:a5,"Fred")

Edited May 19, 2017 by ReadTheNetwork

[Seb1780]

Pivot tables with filters applied will do this.