1

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
A picture of a small part of my sheet, here you can see the different maps in bold colors and the wins, losses and ties that are connected to each map

Sample Results
This is how I am going to showcase the wins, losses and ties in total for each map in the game

3

1 Answer 1

0

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

ARRAYFORMULABYCOLCOUNTIFCOUNTIFSIFERRORLAMBDALETSORTTOCOLUNIQUEVSTACK

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.