counting unique cases which meet two conditions

[The Grim Discovery]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Team
[/TD]
[TD]Name
[/TD]
[TD]Rating
[/TD]
[TD]Man of Match
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]United
[/TD]
[TD]Smith
[/TD]
[TD]10
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]City
[/TD]
[TD]Brown
[/TD]
[TD]9
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]United
[/TD]
[TD]Robinson
[/TD]
[TD]10
[/TD]
[TD]Yes

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]United
[/TD]
[TD]Smith
[/TD]
[TD]10
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]City
[/TD]
[TD]Brown
[/TD]
[TD]8
[/TD]
[TD]No
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]United
[/TD]
[TD]Smith
[/TD]
[TD]9
[/TD]
[TD]yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Excel folk

Once again I turn to your wisdom in my hour of need.
The table above replicates the issue I'm having trying to count uniquely occurring instances.

For instance I need to be able to count the number of times that the team called 'United' (Col A) had a player who scored a rating of 10 (col C) & was also Man of the Match (col D). But I only want to count unique cases. So for United this should return a total of 2 as Smith and Robinson both meet those criteria (albeit Smith does twice).

I've recently had support here from user: Domenic on frequency syntax which has been invaluable to me but I cant stretch the logic to fit the case here. I've tried concatenating the data but it hasn't helped. It's the unique bit that does for me as the first part is evidently countifs.

Thanks in advance

 

[James006]

Hello,

With your data located in range A1:D7

you could test following Array Formula

=SUM(IF(FREQUENCY(IF($B$2:$B$7<>"",IF($D$2:$D$7="Yes",IF($A$2:$A$7<="United",IF($C$2:$C$7=10,MATCH("~"&$B$2:$B$7,$B$2:$B$7&"",0))))),ROW($B$2:$B$7)-ROW($B$2)+1),1))

Hope this will help

 

[The Grim Discovery]

Cheers James - that absolutely nailed it. There's no way I'd ever have got there. Brilliant.
If you don't mind what's the tilde doing after the MATCH function - that's new to me?

 

[The Grim Discovery]

Actually James

I'm finding that the A2:A7 part of the formula appears to be ignoring the name ie. 'United' - thought I might be able to work this out myself. I was wrong...

 

[James006]

Hello,

Sorry for the typo ... you should remove the sign ' lower than ' and keep th equal sign ...

[COLOR=#333333]=SUM(IF(FREQUENCY(IF($B$2:$B$7<>"",IF($D$2:$D$7="Yes",IF($A$2:$A$7="United",IF($C$2:$C$7=10,MATCH("~"&$B$2:$B$7,$B$2:$B$7&"",0))))),ROW($B$2:$B$7)-ROW($B$2)+1),1))[/COLOR]

 

[The Grim Discovery]

Legend.

 

[James006]

You are welcome ...