Count and total items with Summarise

Summarise — Counts, sums or averages fields over all items, by group if you like — works out figures across the items that reach it: the number of orders, their total, the average order value — for everything, or per customer, per region, per day. It works like a pivot table in a spreadsheet.

Who can do this

Workspace Admins and Editors, on every plan.

Steps

1. Select + after the items to summarise. In the step picker, open Data and select Summarise.

2. Under Summarise, fill in a row for each figure: the field, such as total, and what to work out from it:

   Choice	Gives
   Count	How many items have a value in the field.
   Count unique	How many different values there are.
   Sum	The total.
   Average	The mean.
   Min / Max	The smallest and the largest number.
   Join with	Every value as one piece of text, with what you type in the box beside it between each one — *, * unless you change it.

   The first row starts on Sum.

3. Select Add a row for more figures — for example Count of order_no and Sum of total.

4. Optional: in Group by, type one or more field names, separated by commas, such as region. One item comes out per region. Empty: one summary for all items.

5. Select Run this step, then check Output. After a test, the panel says how many went in and came out — 120 items in, 4 out.

Output

One item — or one per group — with a field per row, named by the function and the field: count_order_no, count_unique_customer, sum_total, average_total, min_total, max_total, join_order_no. A field named by a path, such as order.total, is named by its last part: sum_total.

With Group by, each item also has the group's values under their field names — region: Europe, sum_total: 48210.5. Groups come out in the order their first item arrived.

Good to know

• It runs once for all the items that reach it. Inside a loop it summarises that batch only.
• Text that holds a number counts as a number for Sum, Average, Min and Max — 1,499.00 and $1499 both read as 1499 (a leading $, €, £ or ₹ and thousands commas are ignored). Text that is not a number is skipped, not an error.
• Empty values are left out of every function, including Count.
• With no numbers to work from, Sum gives 0, and Average, Min and Max give an empty value.
• Min and Max compare numbers only. For the earliest or latest date, use Sort and keep the first item with Limit.
• Count unique is exact: Northwind Logistics and northwind logistics count as two.
• Sum and Average are worked out in decimals, so money adds up exactly.
• For a readable figure, such as $48,210.50, add Format a number after it — see Calculate, round or format a number with Math.
• To list the values instead of working them out, use Aggregate.

If something goes wrong

Related

• Collect items into one with Aggregate
• Sort items
• Keep some items with Filter
• Query a PostgreSQL database
• Add a row to Google Sheets