ExcelInsider Team
In Excel, tallying a column means calculating the sum of that column. You can use Excel functions and features to tally a column, tally filtered cells, and set conditions for tallying.
To tally a column in Excel, follow the steps below:
➤ Select the cell where you want the total.
➤ In the Home tab, go to Editing >> click AutoSum.
➤ Also, you can select the cell and press Alt = to tally a column.
In this article, we’ll learn different ways to tally a column using the AutoSum, Status Bar, SUM function, and Excel Table. In addition, we’ll tally filtered cells and learn conditional tallying with the SUMIF function.
Tally a Column with AutoSum
In this dataset, we have the date in Column A, the category in Column B, the description in Column C, and the amount in Column D. We will tally the amount column using Excel functions and features. The most effective way to tally a column is to use the AutoSum feature of Excel. Excel will automatically select the range and tally the result.
Steps:
➤ Select the output cell (D12) >> go to Editing >> click AutoSum >> Press enter .
➤ You can see Excel has selected the range and calculated the total for the column.
Using the Status Bar
The status bar can tally a column with just a click. It’s useful for quickly cross-checking the result.
Steps:
➤ Click on the column letter you want to tally and look at the status bar below.
Applying SUM Function
You can use the SUM function to tally a handful of cells or enter a cell range.
Steps:
➤ Select the output cell (D12) and type the following formula:
=SUM(D2:D11)
Using Excel Table
Another easy way to tally a column is using an Excel Table. Tables have additional functionalities and can be imported into other applications like Power Pivot, Power Query, etc.
Steps:
➤ Select a column header >> go to the Insert tab >> click on Table.
➤ Check the range and tick “My table has headers” option.
➤ Move to the Table Design tab >> click on Table Style options >> check Total Row.
➤ In addition to sum, other operations are also available.
Tally a Column with Filtered Cells
You can use the SUBTOTAL and AGGREGATE functions to tally only the visible cells in a column.
Steps:
➤ To apply a Filter, select any column header.
➤ In the Home tab, go to Editing >> click on Sort & Filter >> choose Filter
➤ Click the drop-down >> untick Select All >> check the values to filter by.
➤ Enter the formula in cell D12 to get the result.
=SUBTOTAL(9,D2:D11)
OR
➤ If you’re on a more recent version of Excel, the AGGREGATE function can also tally a column.
=AGGREGATE(9,3,D2:D11)
Note: 1) In the formula, number 9 sums the values, and number 3 ignores hidden rows and error values.
2) Using the SUM function to tally a column with filtered cells will give incorrect results. Since the SUM function considers the visible cells and the filtered-out cells.
Conditional Tallying
You can use the SUMIF function to tally a column based on a condition. This will return the sum of values that satisfy the given condition.
Steps:
➤ Select the output cell (D12) and enter the formula:
=SUMIF(D2:D11,">50",D2:D11)
">50" tells the function to sum values greater than 50.
➤ You can also specify a text condition.
"Food" tells the function to sum the values corresponding to the food category.
FAQ
How do I tally entries in a column in Excel?
To tally only numeric entries in a column use =COUNT(range). To tally both numeric and text entries in a column use =COUNTA(range). Replace the range with the range you want to tally.
What is the formula for running tally in Excel?
Use =SUM($A$2:A2). In a running tally, you have to add the current value to the previous total. The absolute reference (dollar sign) locks the starting point (A2) and the second reference can change when the formula is copied down.
What is the formula to tally cells with text in Excel?
Use =COUNTIF(range, “text”). Replace the range with the cell range containing the texts and enter the text you want to search within double quotes.
Why is the SUM function returning incorrect results?
The SUM function will return erroneous results if the data has filtered cells. Because the SUM function considers both the visible and the invisible (filtered out) cells. Instead, you can use the SUBTOTAL function which always ignores the values hidden by filters.
Why is the SUM function returning zero?
This may occur due to circular reference(s) in the formula. A circular reference occurs when the formula includes the same cell where the result of the formula will be calculated. To solve this, go to the Formulas tab >> Formula Auditing >> Error checking >> Circular References.
Wrapping Up
In this tutorial, we’ve learned various ways to tally a column in Excel. We covered the AutoSum feature, status bar, SUM function, and Excel Table. Then, we tallied a column with filtered cells using the SUBTOTAL and AGGREGATE functions. Lastly, we applied conditional tallying with the SUMIF function. Feel free to download the practice file and let us know which method you like the most.
