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

Question

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}

Answer 1

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 G1or 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}))))

 

---

 

Function Support

ARRAYFORMULA BYCOL COUNTIF COUNTIFS IFERROR LAMBDA LET SORT TOCOL UNIQUE VSTACK