MrExcel.com - Your One Stop for Excel Tips & Solutions

Total Without Using a Formula

December 06, 2021 - by

Total Without Using a Formula

Problem: My manager called on the telephone, asking for the total sales of a particular product. I need to quickly find a total. Is there a faster way than entering a formula?

Strategy: While you’re on the phone with your manager, you can highlight the numbers in question. The QuickSum indicator in the status bar will show the total of the highlighted cells.

Three numbers are selected in the grid (21, 36, and 22). Look in the Status Bar at the bottom and Excel reports Sum: 79.
Figure 201. Select numeric cells, and the total appears in the status bar.

Additional Details: The status bar can simultaneously show a count, a numeric count, a sum, and so on. Right-click the status bar and choose the statistics you would like to show.

Gotcha: The Average, Numerical Count, and Sum parts of the status bar will ignore text entries within the selection. Below, Sum and Numerical Count only factor in B2:B3.

This time, three cells are selected. One is the word Total. The other two cells contain 91 and 31. The Status Bar is reporting Average: 61, Count: 3, Numerical Count 2, Sum: 122
Figure 202. Count, Numerical Count, and Sum will ignore text cells.

If one of the highlighted cells is an error such as #N/A, the Sum and Average statistics will not appear in the status bar.

Three cells are selected containing 91, 115, and the #N/A error. With an error cell in the selection, that status bar reports Count: 3, Numerical Count: 2. There are no stats for Average or Sum.
Figure 203. An error cell will cause the Sum statistic to disappear.

Sometimes, when you are trying to find a lone #N/A within a column, it is fastest to start at the top of the column, hold down the Shift key, and start pressing PgDn. As soon as the Sum statistic disappears, you know that you have recently paged past the first #N/A error. (With 1 million rows, it might be faster to use Home, Find & Select, Go To Special, Errors.)

This article is an excerpt from Power Excel With MrExcel

Title photo by Neha Deshmukh on Unsplash