Sort, filter and count in Google Sheets

Four spreadsheet moves cover everything this guide asks you to do with a data file. Three traps give you wrong numbers without any error message, so they matter more than the moves. Every example uses the Shark Control Program file from the worked example.

1. 1

   Sort the whole sheet

   Sorting is how you read a file. Sort by name, and gaps and odd rows show up. Sort by a number, and you see the smallest and largest values, the zeros, and anything negative.

   1. Freeze the header row first: View › Freeze › 1 row. Otherwise the headings get sorted in with the data.
   2. Click any cell in the column you want to sort by.
   3. Choose Data › Sort sheet › Sort sheet (A to Z). Choose Z to A to put the largest first.
   In the Shark Control Program file

   Sorting NumberCaught from largest to smallest took a minute. The top rows record several sharks at once, which shows that a row is not one animal: it is one kind of animal on one gear at one beach in one month. 4,003 rows in the file record more than one, and nothing else in the download says so.

   Watch out: sorting one column scrambles your data
2. 2

   Filter to your inclusion rule, then copy to a new tab

   Your inclusion rule says which rows count. A filter hides the rest, so you can look at what is left before you use it.

   1. Click any cell in the data and choose Data › Create a filter. A filter button appears in every heading.
   2. Click the button in a heading. Use Filter by values to tick the values you want to keep, or Filter by condition for a rule such as Greater than.
   3. Select the rows you can see, copy them, and paste them into a new tab. Only the visible rows are copied. Name the tab after your rule, for example 1996 countries.
   4. Do every calculation on that new tab.
   In the Shark Control Program file

   Filter Year by condition to Is between 1996 and 2023, SpeciesGroup to SHARK, Gear to Net and Drum, and Area to Gold Coast, Sunshine Coast North, Sunshine Coast South and Rainbow Beach. That leaves 4,299 rows, which together hold 5,228 sharks.

   Watch out: filtered rows still count Watch out: a filter on a shared sheet changes it for everyone
3. 3

   Count and average by group

   Most questions compare groups. One formula per group gives you the count, the mean and the median, and a reader can see how each number was made.

   =SUMIFS(O:O, F:F, "Net")

   The total of column O (NumberCaught) for the rows where column F says Net.

   =SUMIFS(O:O, F:F, "Net", P:P, "White shark")

   The same, for the rows that also say White shark in column P.

   =AVERAGEIF(F:F, "Net", O:O)

   The mean of column O, for the rows that say Net.

   =MEDIAN(FILTER(O:O, F:F="Net"))

   The median of column O, for those rows only.

   Copy each formula and change the text in quotes for your other group. The text has to match the cells exactly, spaces included, or the formula finds nothing.

   In the Shark Control Program file

   With a helper column P that says White shark or Other shark (=IF(J2="WHITE SHARK", "White shark", "Other shark")), these give 3,296 sharks in nets, 73 of them white sharks, and 1,932 on drumlines, 74 of them white sharks. Add up NumberCaught with SUMIFS: counting rows would count each row once, however many sharks it records.

4. 4

   Check the count

   The number of rows you analysed has to match the number in your method. If it does not, a row has gone missing or crept in, and every result after it is wrong.

   =COUNTA(A2:A)

   How many filled cells there are in column A, below the heading.

   Compare it with the number in your method. Then count each group with COUNTIF: the groups should add up to the total.

   In the Shark Control Program file

   4,299 rows, and =SUM(O2:O) gives 5,228 sharks: 3,296 in nets and 1,932 on drumlines. 3,296 + 1,932 = 5,228.

Each one gives you a wrong number, and none of them shows an error.