COUNTIF With Multiple Conditions Across a Range

[3ch03s]

Hello.

Forgive me if this has been answered elsewhere. I have looked but I'm having difficulty matching my situation to others. Or perhaps I'm just not understanding how to use COUNTIF/COUNTIFS/SUMPRODUCT.

Here's my situation:

I'm hoping to create a matrix that displays the number of Home Wins, Away Wins, Home Losses and Away Losses of each team in a competition versus the other teams based on a list of results stored on a second worksheet.

To do so I need a formula to count the number of occurences where the following 3 conditions are met: Winning team = "Home"; Home team = "Team 1"; and Away team = "Team 2".

Ultimately I can edit and fill the formula to match each square in the matrix.

Thank you!

 

[T. Valko]

Hello.

Forgive me if this has been answered elsewhere. I have looked but I'm having difficulty matching my situation to others. Or perhaps I'm just not understanding how to use COUNTIF/COUNTIFS/SUMPRODUCT.

Here's my situation:

I'm hoping to create a matrix that displays the number of Home Wins, Away Wins, Home Losses and Away Losses of each team in a competition versus the other teams based on a list of results stored on a second worksheet.

To do so I need a formula to count the number of occurences where the following 3 conditions are met: Winning team = "Home"; Home team = "Team 1"; and Away team = "Team 2".

Ultimately I can edit and fill the formula to match each square in the matrix.

Thank you!

If you could post a small amount of sample data it would help us understand your setup.

 

[Willmonbo]

try this

=COUNTIFS(A1:A10,"Home",B1:B10,"Team 1",C1:C10,"team 2")

 

[3ch03s]

Hi Guys.

Firstly, thank you both for your prompt replies. I'm not sure how to best paste the data. Unfortunately It doesn't look as though I can upload direct to this forum.

I've used what essentially was Willmonbo's formula and it seems to be working correctly at this point so thank you for your advice.

If you'd like to know (and I'd love some input), these are the formulas I have been using:

1. =COUNTIFS(Data!$F:$F,"Home",Data!$B:$B,C$1,Data!$D:$D,$B4)
2. =COUNTIFS(Data!$F:$F,"Home",Data!$B:$B,C$1,Data!$D:$D,$B4)
3. =COUNTIFS(Data!$F:$F,"Away",Data!$B:$B,$B4,Data!$D:$D,C$1)
4. =COUNTIFS(Data!$F:$F,"Away",Data!$B:$B,$B4,Data!$D:$D,C$1)

Set up in a 2x2 matrix that looks like this:

12
34
​

Intending to calculate the following matrix where results are for the team indicated in the header row against the team in the left-most column:

Home Wins | Home Losses
Away Wins | Away Losses
​

Where:
C1 is the first cell reference for home teams (they rise by a factor of two [C1,E1,G1 etc.] to account for the win and loss columns)
B2 is the first cell reference for away teams (also rise by a factor of two [B2,B4,B6] to account for Home and Away rows).

and:

Data!A:A is the Game Date
Data!B:B is the Home Team Name
Data!C:C is the Home Team Score
Data!D:D is the Away Team Name
Data!E:E is the Away Team Score
Data!F:F is the Winning Team's Location (either "Home", "Away" or "TBC" for games not yet played)
Data!G:G is the Winning Team's Name (probably unnecessary as not used in the formulas)
and Data!G:G is the Losing Team's Name (again probably unnecessary).

If anyone knows where I could upload the spreadsheet, I'd be more than happy too.

 

[3ch03s]

Bah! I can no longer edit the post above .

I've since added named ranges so that the formulas above are now:

1. =COUNTIFS(Winner_Loc,"Home",Home_Team,E$1,Away_Team,$B2)
2. =COUNTIFS(Winner_Loc,"Away",Home_Team,E$1,Away_Team,$B2)
3. =COUNTIFS(Winner_Loc,"Away",Home_Team,$B2,Away_Team,E$1)
4. =COUNTIFS(Winner_Loc,"Home",Home_Team,$B2,Away_Team,E$1)

where: [Winner_Loc] refers to Data!F:F, [Home_Team] refers to Data!B:B and [Away_Team] refers to Data!D:D.

This makes everything a lot easier on the eyes. If only I could move this post before the last one

Thanks again everyone!