How to Sum in Excel (All Things You Need to Know)

In this article, you will learn all things you need to know about how to sum in Excel.

The most used function for calculating sum is the SUM function. It allows users to quickly and accurately calculate the total of a range of numbers or cells. It simplifies complex calculations by providing a straightforward method.

There are some other available functions and features for finding a sum that we can consider depending on the situation and the use case.

This tutorial will provide you with a complete guideline on summing rows and columns, sum filtered cells, sum on condition, sum specific values, etc.

Download Practice Workbook

Sum a Range of Cells with AutoSum Feature

To apply the AutoSum feature for summing a range of cells:

• Select a cell where you want to apply autosum > go to the Home tab > click AutoSum under the Editing group.

• This will insert the SUM formula.
• Press ENTER.

Hence Excel will automatically calculate the sum of the range.

Sum Selected Cells from Excel Properties

Just click on the Column name that contains your data. At the bottom section of the worksheet window, Excel will show you some properties of the selected range. You can get the sum from here.

Sum Single or Entire Column in Excel

To sum a column in Excel, you can use the SUM function directly. Apply the following formula to calculate the sum of an entire column.

This formula will calculate the sum of cells ranging from D5 to D11.

Find Sum of Filtered Cells with SUBTOTAL Function

For a filtered range of cells, we have applied the SUBTOTAL function to find the sum.

We found the Total Quantity => 26.

For the data below, we want to find the sum of Monitor only, so we need to filter the Item column for Monitor.

• Click on the filter icon (the dropdown arrow) on the Item column.

• Mark only Monitor and click OK.

• This command will calculate the sum only for Monitor. So the total output will be decreased as now the concerning item is only the Monitor.

Convert Range to Table for Calculating Sum

To convert your data range to a table for finding the sum, act as below:

• Select a random cell in the range > go to Insert tab > click Table.

• Click OK on the Create Table dialog box.

• After creating a Table, go to the Table Design tab > put a checkmark on Total Row.

This will insert a new column that will show you the sum of the numbers.

Calculate the Sum of Running Factors

The dataset below represents the statement of a balance sheet. In column B, we have some transactions. Follow the formula below for the first factor.

Then, drag the Fill Handle tool down. This will Autofill the formula and calculate the running total for the factors.

Calculate Sum Based on Condition in Excel

Use SUMIF Function to Match Single Criterion

From the dataset below, we want to get the sum of product quantity for Mike. So, we have only a single criterion here. The SUMIF function calculates the sum for a range based on a single criterion.

This formula calculates the sum in the range D5:D14 based on the cell value of B5.

Use SUMIFS Function to Match Multiple Criteria

If you want to calculate the total quantity of Monitor purchased by Mike, in that case, you can use the SUMIFS function. The SUMIFS function calculates some based on multiple criteria.

Sum Every Nth Row in Excel

To sum up every nth row, you can use a combination of SUM, MID, and ROW functions. Apply the following formula to sum every 2nd row from your dataset.

This formula takes only every 2nd row in the range D5:D14.

This is an array formula. So don’t forget to press CTRL+SHIFT+ENTER to apply the formula.

Sum Largest Values in Excel

To find the sum of a specific number of largest values, you can use a combination of the SUM and the LARGE functions. Apply the following formula to find the top 3 largest values in your dataset.

This formula will find the sum of the top 3 numbers in the range D5:D14.

Sum Range with Text Values and Error in Excel

If your sum range contains some text values or errors, you can use the IFERROR function in that context. The following formula can serve your needs.

When the formula finds some text or errors, it considers 0 in that place and sums with the range.

Frequently Asked Questions

1. What is the sum range?

The sum range refers to the range of cells that you want to add together. It is the set of cells that you specify as the input for the function to calculate the sum.

2. What is subtotal in Excel?

In Excel, the SUBTOTAL function is used to calculate various types of subtotals within a range of data. The SUBTOTAL function is particularly useful when working with filtered data or when you want to calculate subtotals for specific sections within a larger dataset.

Key Takeaways from the Article

• The SUM function is the most used function to calculate the sum in Excel.
• SUMIF and SUMIFS functions are used to sum cells based on condition.
• The SUBTOTAL function is used to find the sum of filtered cells.
• AutoSum feature inserts the SUM function to calculate the sum.
• Texts and errors can be handled with the IFERROR function.

Conclusion

So, we have discussed various aspects and situations on how to sum in Excel. The concept of summing in Excel encompasses a range of powerful functions that go beyond the basic SUM function.

Functions like SUMIF, SUMIFS, AutoSum, and SUBTOTAL provide users with enhanced capabilities to perform specific calculations and handle complex datasets.

Depending on the use case, users can choose the specific function they need.

Sum in Excel: Knowledge Hub

<< Go Back to How to Calculate in Excel | Learn Excel