Totals for every day and payment
The shop tallies each sale on a long list. The manager wants a small grid: one row for each day, one column for each way to pay, and the items sold in every box.
Adding by hand is slow. Summary formulas do it in a flash.
Key words
| Word | What it means |
|---|---|
| COUNTA | counts the cells in a range that are not empty |
| COUNTIFS | counts the rows that pass two or more tests |
| SUMIFS | adds a column for only the rows that pass two or more tests |
| Pivot table | a grid that summarises a long list, with one field down the side and one across the top |
| Summary | one number that stands for many rows, such as a total or a count |
| Database | an organised store of tables that you can ask questions |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can use COUNTA, COUNTIFS, SUMIFS and a pivot table idea to summarise a list.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
No spreadsheet or pivot table here
Important
This platform has no spreadsheet program. Here the cells are a table and the formula is shown as text. You do the same job with code. On site you use a real spreadsheet program. A pivot table is shown as a result grid made with DuckDB.
The tally list as cells
| Row | A day | B paid by | C items |
|---|---|---|---|
| 1 | Mon | card | 3 |
| 2 | Mon | cash | 2 |
| 3 | Tue | card | 1 |
| 4 | Tue | card | 4 |
| 5 | Wed | cash | 5 |
| 6 | Wed | card | 2 |
| 7 | Wed | cash | 1 |
The cells students fill in are left blank in this preview.
Three summary formulas
| Formula | Does | Answer |
|---|---|---|
| `=COUNTA(A1:A7)` | Counts cells that are not empty | 7 |
| `=COUNTIFS(A1:A7,"Tue",B1:B7,"card")` | Counts rows with Tue and card | 2 |
| `=SUMIFS(C1:C7,A1:A7,"Tue",B1:B7,"card")` | Adds items for Tue and card | 5 |
The cells students fill in are left blank in this preview.
Try it: the same summaries in SQL
SELECT COUNT(day) AS counta FROM tally;
SELECT COUNT(*) AS countifs
FROM tally WHERE day = 'Tue' AND paid_by = 'card';
SELECT SUM(qty) AS sumifs
FROM tally WHERE day = 'Tue' AND paid_by = 'card';
A pivot table is a grid of summaries
A pivot table turns the long list into a small grid. Days go down the side. Ways to pay go across the top. Each box is a summary of the rows that fit, here the sum of items.
In a spreadsheet you drag fields into rows, columns and values. In DuckDB you write PIVOT.