Sum or count values on a datasheet with a Total row

A Totals row helps you see, at a quick glance, what the totals are for columns on a datasheet. For example, in a table of purchase information, we can show the sum of the price, or units purchased, or a total count of the items by adding a Totals row to the datasheet:

Totals row complete with data

Note:  To show the Sum value for a column, the column’s data type has to be set to Number, Decimal or Currency. For non-numeric columns, you can only select the Count total type.

Add a Totals row

  1. Double-click the table, query, or split form from the Navigation Pane to open it in Datasheet View.

  2. On the Home tab, click Totals.

    Access Ribbon Image

    A Total row is added at the end of your datasheet:

    totals row on datasheet

  3. For each cell in the Total row where you want a total to appear, click in the cell and select the kind of total you want.

Selecting the total type

After adding the Total row, you select what type of totals value is displayed for each column. For example, aggregates like sums can be displayed when the data type is set to Number, Decimal, or Currency. A count of values is possible when the data type is a text value.

Let’s have the datasheet show us the total sum value for the Purchase Price and Units Purchased columns and the total count for the Item column in the image below:

  1. Instead of the word Total in the first column, we can show a total record count. Click the totals row for the Item column, click the arrow and select Count to see the total number or count of items.

  2. Next, click the arrow in the Purchase Price column and select Sum to see the total purchase price of all items.

  3. Repeat the previous step for the Unit Purchased column to see the totals for the units purchased.

totals row with sum and count functions

Note:  Though the Total row can’t be deleted or cut, it can be hidden by clicking Totals on the Home tab.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!