Xero products and services in a live spreadsheet
Add the Products and services table to a sheet as a data source named products_and_services and its 14 columns become names your formulas, pivot tables and charts use. Sheets reads it from your Xero organisation each time the sheet opens or refreshes.
Formulas on this table
=data.products_and_services.sum(sales_price) Total sales price. Adds up the Sales price column over every row of Products and services.
=data.products_and_services.sum(sales_price, sales_account, A2) Sales price per sales account. With a sales account in A2, the total for that one. Fill it down next to a list of them.
=list.products_and_services(sales_account) Every sales account, once. Spills the distinct sales account values down the column: the row labels for the formula above.
=data.products_and_services.count(id, sales_account, "<>") Rows with a sales account. Counts the rows whose Sales account is not empty.
=data.products_and_services.avg(cost) Average cost. avg, min, max, count, countd (distinct) and first work the same way as sum.
Columns
| Column | In formulas | Type |
|---|---|---|
| ID | id | Text |
| Code | code | Text |
| Name | name | Text |
| Description | description | Text |
| Sales price | sales_price | Number |
| Sales account | sales_account | Text |
| Cost | cost | Number |
| Purchase account | purchase_account | Text |
| Tracked | tracked | Yes / no |
| Quantity on hand | quantity_on_hand | Number |
| Inventory value | inventory_value | Number |
| Sold | sold | Yes / no |
| Purchased | purchased | Yes / no |
| Updated at | updated_at | Date and time |
Other lists tables
Your products and services, live
Invoices, bills, bank transactions and your monthly profit and loss become tables that formulas, pivots and charts read directly, in your base currency.