# How to count wins, losses and ties based on multiple conditions

I'm very new to Google Sheets.

I have a sheet where I record the outcomes of games played.

• Each row represents a single game
• Column A, `Map`, stores the name of the map that was played.
• Column D, `Result`, contains either Win, Loss, or Tie.

## Sample Data

A B C D
1 Map Result
2 Mirage 10/13 15/3/14 Loss
3 Nuke 113:7 18/1/13 Win
4 Inferno 13:10 18/3/17 Win
5 Mirage 116:12 13/3/23 Win
6 Mirage 115:15 13/5/22 Tie
7 Inferno 1:9 23/8/22 Loss
8 Vertigo 17:13 6/1/9 Loss
9 Nuke 113:7 15/6/16 Win
10 Inferno 13:10 9/6/18 Win
11 Vertigo 116:14 24/21/5 Win
12 Overpass 13:11 18/14/2 Win
13 Mirage 8:13 14/15/2 Loss

## Count by Result

I created a formula using COUNTIF that returns the total count based on the `Result` (outcome) entered in the cell above it.

``````=COUNTIF(\$D\$2:\$D\$13, G\$1)
``````
• formula is in `G2` and was also copied to `H2` and `I2`
F G H I
1 MAP WIN LOSS TIE
2 All 7 4 1

# Count by Result and by Map

Similar to my formula above, I'd like to return the total count but by `Map`. For example, if the `Map` is Mirage then the total wins, losses, and ties across all games played on the Mirage map only.

How can I do that?

#### Desired Results

F G H I
1 MAP WIN LOSS TIE
2 All 7 4 1
3
4 Mirage 1 2 1
5 Overpass 1 0 0
6 Vertigo 1 1 0
7 Inferno 2 1 0
8 Nuke 2 0 0

Sample Data

Sample Results

• One of the concepts that you might need to learn is data structure, and more specifically, simple table. While spreadsheets are very flexible, I suggest you ensure that your data is entered in the form of simple table. The top row should have column headers, which should be unique. Once you have set your data this way, you might use the pivot table and the QUERY function to achieve the result efficiently. The second image shows a double-entry table with column and row headers.
– Rubén
Oct 13 at 22:45
• In this case, the headers are grouping data and the intersection shows an aggregation calculation. This is frequently referred to as "pivot", hence the name of the tool "pivot table".
– Rubén
Oct 13 at 22:51
• – Rubén
Oct 13 at 22:56

# QUERY Approach

* Credit to Rubén

``````=QUERY(A:D,
"Select A, count(A)
where D is not NULL
group by A pivot D", 1 )
``````

# QUERY w/ Total Row

``````=LET(q,QUERY(A:D,
"Select A, count(A)
where D is not NULL
group by A pivot D", 1),
{q; "", "", "", "";
"TOTAL",
INDEX(QUERY("Select
sum(Col2), sum(Col3),
sum(Col4)",1),2)})
``````

# COUNTIFS Approach

Use COUNTIFS to allow an additional condition.

In `G4` adapt your existing formula with an additional condition that `Map` matches the value in `F4` as follows:

``````=COUNTIFS(\$D\$2:\$D, G\$1,
\$A\$2:\$A, \$F4)
``````
• you can copy this formula to the rest of the cells that need it (eg. `G4:I8`).

# Multi-Row Formula

• Places the Basic Formula inside ARRAYFORMULA so the formula in `G4` will fill all the necessary rows below it without copying it down.
• Works on a single column so needs to be copied to `H4` and `I4`
``````=ARRAYFORMULA(
COUNTIFS(
\$D2:\$D, G1,
\$A2:\$A, \$F4:\$F8))
``````

# Multi-Column & Row

• A single formula populates all the counts in `G4:I8` (both columns and rows).
• BYCOL passes each of the values in `G1:I1` one-by-one into a LAMBDA that stores the current one in the variable `_col`
• LAMBDA's formula is based on the Multi-Row Formula but the variable `c` replaces the cell references to `G1`or `H1` or `I1`.
``````=BYCOL(G1:I1, LAMBDA(c,
ARRAYFORMULA(
COUNTIFS(
D2:D, c,
A2:A, F4:F8))))
``````

# All Labels & Values

• Single formula in `F1` that creates all headings, lookup values, and result counts.
``````=LET(maps,A2:A, wlt,D2:D,
umaps,UNIQUE(TOCOL(maps, 1)),
uwlt,{"WIN", "LOSS", "TIE"},
ttls,BYCOL(uwlt, LAMBDA(u,
ARRAYFORMULA(COUNTIFS(
wlt, u, maps, umaps)))),
all,{"All", BYCOL(ttls,
LAMBDA(t, SUM(t)))},
IFERROR(VSTACK(
{"MAP", uwlt}, all,,
SORT({umaps, ttls}))))
``````