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**

**Table of Contents**Expand

## 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.

`=SUM(D5:D11)`

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.

`=SUBTOTAL(9,D5:D14)`

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.

`=SUM(C$5:C5)`

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.

`=SUMIF(B5:B14,B5,D5:D14)`

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.

`=SUMIFS(D5:D14,B5:B14,B9,C5:C14,C9)`

## 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.

`={SUM(D5:D14*(MOD(ROW(D5:D14),2)=0))}`

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.

`=SUM(LARGE(D5:D14,{1,2,3}))`

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.

`=SUM(IFERROR(D5:D14,0))`

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**