如何求和Excel中的一列

Sum an entire column with only one click

You can sum columns in Microsoft Excel with just one click. Simply click on the column's name you want to add, and it will automatically highlight the column and sum values in that entire column. You can see the sum in the Excel status bar. The Excel status bar is at the bottom of the Microsoft Excel application.

The advantage of using the status bar for the sum value is that you also get the average value of all column values and the count number of total values.

One more advantage is that if there is an empty cell or text-written cell between the valued cells, it will ignore that cell.

One disadvantage of using that method is that you cannot copy the summed-up value.

You can also customize the status bar by right-clicking on the status bar. You can add maximum and minimum values, etc.

AutoSum formula to add column

The AutoSum function helps to sum an entire column with just a few steps:

1. There are two ways to use the AutoSum button.
2. You can find the button on the Home tab and on the Formulas tab.

3. On the Home tab, go to the Editing group and select the AutoSum function; or, you can go to the Formula tab, and there you can see the AutoSum function in the Function Library group.
4. There are two methods to sum a column.
5. The first is to select the values of the cells by clicking and dragging the columns. Then, press the AutoSum button. The values of the selected columns will be added at the end of the sum value column.
6. The second method is to select the empty cell below the value and press the AutoSum button.
7. This will automatically add the sum function and highlight the range of the numbers.
8. Press Enter. You will get the total sum.

You can also use this feature to sum up the rows. Just select the next row of the values in row cells, and the sum function will show the range. Then press Enter, and you will get the result value in the new row.

Use the SUM function to manually sum a column in Microsoft Excel

The AutoSum options are quicker and more accessible. There are some advantages to using manual functions to sum columns. You can add the SUM function manually or sum only certain cells in a column, or specify an address for a wide range instead of selecting them manually.

Below are the steps to manually use the SUM formula:

1. Firstly, select a cell anywhere on the Excel table where you want your sum value.
2. Enter =SUM( by keyboard keys, or you can also use the AutoSum function.

3. Next, select the range of numbers you want to sum.
4. You can select the range of columns by selecting the first cell in the column and holding the Shift key to select the last value cell.
5. You can also select separate cells by left-clicking on the cells while holding the Ctrl key.
6. Go ahead and select the desired cells.

7. Now, press the Enter key, and you will see your result in the selected cell.

Note that when using the SUM function to get the sum of a column, any filtered or hidden cells are also included.

Using the SUM function to add only visible cells

If you have a data table where you have hidden or filtered cells, you cannot use the SUM formula directly. There are alternative steps to sum these values. Follow the steps below to sum visible cells only:

Hidden Cells

• Function_num: This is a number that tells the AGGREGATE feature the calculation that should be made. In this example, I have used nine, which represents the SUM function.
• Options: You'll be able to specify what you wish to ignore when doing the calculation. In this example, I have used 3, which ‘Ignores hidden row, error values, nested SUBTOTAL, and combination functions’. In short, it solely uses the visible cells for the calculation.
• Array: Select the range of the cells from which you want the sum of their values. For example, D1:D3 is the range of cells that may also contain hidden cells.

=AGGREGATE(9,3,D1:D3)

This formula provides the sum of the visible cells.

If you're using an older version of Excel, you can use the following formula:

Filtered Cells

With this formula, we can sum a column in Excel with filtered cells present. Use the SUBTOTAL function:

=SUBTOTAL(9,D1:D3)

This formula calculates the subtotal and only considers the visible cells when summing.

The IronXL C# Library

For the opening, reading, editing, and saving of Excel files in .NET, IronXL provides a versatile and powerful framework. It is compatible with all .NET project types, including Windows apps, ASP.NET MVC, and .NET Core Applications.

For .NET developers, IronXL provides a simple API for reading and writing Excel documents.

To access Excel manipulation scripts, IronXL does not require the installation of Microsoft Office Excel on your server, nor the use of Excel Interop. Working with Excel files in .NET becomes incredibly rapid and straightforward as a result of this.

Using IronXL, developers can perform all Excel-related calculations without any complication by simply writing a few lines of code for tasks such as adding two cells, the grand total columns option, adding an entire column to an Excel table, adding an entire row to an Excel table, all the cells sum function/sum option and sum function of multiple columns and multiple rows, and many other helpful features.

Below are the code examples of the C# code in action.

using IronXL;

// Load the existing Excel workbook
WorkBook workbook = WorkBook.Load("test.xlsx");
// Get the default worksheet
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set formulas to specific cells in the worksheet
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculation of all formula values in all sheets
workbook.EvaluateAll();

// Retrieve and print the calculated value of the formula in cell G30
string formulaValue = worksheet["G30"].Value;
Console.WriteLine($"Calculated Value at G30: {formulaValue}");

// Retrieve and print formula as a string from cell G30
string formulaString = worksheet["G30"].Formula;
Console.WriteLine($"Formula in G30: {formulaString}");

// Save changes to the workbook with updated formulas and calculated values
workbook.Save();

using IronXL;

// Load the existing Excel workbook
WorkBook workbook = WorkBook.Load("test.xlsx");
// Get the default worksheet
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set formulas to specific cells in the worksheet
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculation of all formula values in all sheets
workbook.EvaluateAll();

// Retrieve and print the calculated value of the formula in cell G30
string formulaValue = worksheet["G30"].Value;
Console.WriteLine($"Calculated Value at G30: {formulaValue}");

// Retrieve and print formula as a string from cell G30
string formulaString = worksheet["G30"].Formula;
Console.WriteLine($"Formula in G30: {formulaString}");

// Save changes to the workbook with updated formulas and calculated values
workbook.Save();

Imports IronXL

' Load the existing Excel workbook
Private workbook As WorkBook = WorkBook.Load("test.xlsx")
' Get the default worksheet
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

' Set formulas to specific cells in the worksheet
Private worksheet("A1").Formula = "Sum(B8:C12)"
Private worksheet("B8").Formula = "=C9/C11"
Private worksheet("G30").Formula = "Max(C3:C7)"

' Force recalculation of all formula values in all sheets
workbook.EvaluateAll()

' Retrieve and print the calculated value of the formula in cell G30
Dim formulaValue As String = worksheet("G30").Value
Console.WriteLine($"Calculated Value at G30: {formulaValue}")

' Retrieve and print formula as a string from cell G30
Dim formulaString As String = worksheet("G30").Formula
Console.WriteLine($"Formula in G30: {formulaString}")

' Save changes to the workbook with updated formulas and calculated values
workbook.Save()

Developers have to be careful when they set out to modify and edit Excel files in C# because it can be easy for one misstep to change the whole document. Being able to rely on simple and efficient lines of code helps reduce the risk of error, and makes it easier for us to edit or delete Excel files programmatically. Today, we walked through the steps necessary to edit Excel files in C# quickly and accurately, using functions that have already been well-tested. For more information, please read How to Edit Excel Files.