Sum Cells in Excel: Continuous, Random, With Criteria, etc.

Summing different cells is one of the most common and essential operations in Excel. There are many ways to do it. In this article, I’ve covered 4 of the most popular and straightforward techniques for performing sum operations in various scenarios.

Download the Worksheet

You can download the worksheet that I used in this article from below and practice with it by yourself.

4 Ways to Sum Cells in MS Excel

The following image is of the dataset which is used throughout this article. It has 3 columns (Date, Sales Rep, and Sales). I have used different cells from this dataset to explain the 4 subsequent methods.

The dataset

1. Using simple arithmetic calculations

You can perform a sum operation between cells by adding cells with a plus (+) operator.

Follow these steps to apply this solution:

  1. Select the cell you want to show the summation to (In my case, cell C19).
  2. Enter equal (=) sign in the cell. It should enable you to type formulas.
  3. Now, type in the desired cell numbers with a plus (+) operator in between (In my case, C3+C5+C7) and hit the Enter button.Summation using the plus (+) operator

It will sum the values of the cells you entered and get you the output in the selected cell.

[Note: This method is handy when working with a small number of cells, but in terms of working with a large dataset, following this method may be troublesome as you have to type in every cell number.]

Read More: [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)

2. Using the “SUM” function

The syntax of the Excel SUM function is as follows:

=SUM(number1, [number2] ,…)

The SUM function takes 3 types of inputs: positive or negative numeric values, range, and cell references. It takes these inputs and shows their summation as output.

The 1st argument is mandatory, others are optional, and it takes up to 255 numbers.

Follow these steps to get the summation using the Excel SUM function:

  1. Select the cell you want to show the summation to (In my case, cell F4).
  2. Enter equal (=) sign in the cell. It should enable you to type formulas.
  3. Type in “SUM” or select the SUM function from the drop-down menu.Selecting the SUM function
  4. Select the cells you want to get the summation of (In my case, C1, C5, C7) and hit Enter button.Summation using the SUM function

It will sum the values of the cells you entered and get you the output in the selected cell. You can choose a range of cells or multiple columns and rows as well.

Read More: How to Sum Only Positive Numbers in Excel (4 Simple Ways)


Similar Readings


3. Using the “AutoSum” option

You can also use the “AutoSum” option to perform the sum operation automatically. This is the easiest method as Excel automatically handles everything. You just have to click the option, and Excel will insert the formula for you.

Follow these steps to apply this solution:

  1. Select the cell you want to show the summation to (In my case, cell C17).Selecting the output cell
  2. Go to Home and click the “AutoSum” option under the Editing section or press Alt + = (Equal sign) and hit Enter button.Selecting the "AutoSum" option

It will automatically insert a formula with the SUM function and appropriate arguments based on the adjacent cells (In my case, the Sales column) and show you the summation in the selected cell.

Formula generated by the "AutoSum" option

[Note: As it is an automated process, It may work differently in your dataset and might show results that you don’t want. In that case, you have to follow the second method and apply the SUM function manually.]

4. Using the “SUMIF” function

The syntax of the SUMIF function is as follows:

=SUMIF(range, criteria, [sum_range])

range: The range of cells on which the criteria should be applied.

criteria: The criteria that determine which cells to add.

sum_range [Optional]: The cells to add together. If the sum_range is not specified, the range’s cells are added together instead.

By using this method, you can get the summation of specific cells based on certain criteria.

Follow these steps to apply this solution:

  1. Select the cell you want to show the summation to (In my case, cell F12).
  2. Enter equal (=) sign in the cell. It should enable you to type formulas.
  3. Type in “SUMIF” or select the SUMIF function from the drop-down menu.
  4. Select the range and add a comma (In my case, the Sales Rep column).
  5. Select the criteria and add another comma (In my case, cell F12).
  6. Now, select the sum_range (In my case, the Sales column) and hit the Enter button.Summation using the SUM function

It will show you the summation result of the criteria-based cells (In my case, It will only sum the cells in the Sales column where the Sales Rep name is Abigail). This tool is very handy when you are working with a large dataset.

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)

Conclusion

In general, summing cells in MS Excel is a pretty easy task, but it can be hideous for large datasets. In this article, I’ve provided multiple methods for multiple scenarios so that no matter how large the dataset is, you always get the solution. I hope you were able to solve your problem. Please leave a comment if you have any suggestions or questions. Thank you.


Further Readings

Chinmoy Mondol

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo