CODEWITHSUNDEEP
excelexcel-formula
KOB
KOB

Reputation: 4545

Count number of unique combinations of two columns

I have a spreadsheet of statistics from sports games over a season, for different leagues - each row holds a single event that happened in a game, such as a penalty. There are many rows of events for each individual game. One of the columns is the league, another is the home team and another is the away team. How can I count the total number of games in a given league? In other words, I would need to count the number of unique pairs of strings from Home and Away, where League = "Ligue 1".

EDIT

I have tried:

=SUMPRODUCT(1/(COUNTIFS(E2:E81078,"Ligue 1",F2:F81078,F2:F81078,G2:G81078,G2:G81078)))

which returns a DIV/0 error (it does work if I dont include the column E = "Ligue 1" criteria).

Upvotes: 1

Views: 3073

Answers (3)

Kashish Mirpuri
Kashish Mirpuri

Reputation: 1

EXCEL SCREENSHOT=SUMPRODUCT(1/COUNTIFS($B$1:$B$7,B1:B7,$C$1:$C$7,C1:C7))

TRY THIS =SUMPRODUCT(1/COUNTIFS($B$1:$B$7,B1:B7,$C$1:$C$7,C1:C7))

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You can do this with a Pivot Table.

Add a "helper" column where you concatenate the two teams, preferably with a delimiter in between, eg:

=CONCATENATE(B2, "|", C2)

Use, for example Teams for the column header

Then, Insert ► Pivot Table and be sure to select to Add to Data Model

enter image description here

This adds the option for Distinct Counts to the Values Settings

Then Drag "league" to the Rows area, "Teams" to the Values area, and select Distinct Count for the Value Setting

enter image description here

You might get a table similar to below, which you can format in many different ways:

enter image description here

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

This is similar to your formula but deals with the division by zero

=SUM(IFERROR((1/COUNTIFS(E2:E81078,"Ligue 1",F2:F81078,F2:F81078,G2:G81078,G2:G81078)),0))

Enter it with Ctrl+Shift+Enter rather than just Enter. If done correctly you will see {} around the formula

Try not to use ranges that are bigger than your data because it will slow these kind of formulas down significantly

Update

This might also work if your data is ordered the way you show in your question. It counts the number of times the home team changes in Ligue 1 data :

=SUMPRODUCT((F3:F81079<>F2:F81078)*(E2:E81078="Ligue 1"))

Note that the ranges in column F are offset by one row

Upvotes: 1

Related Questions