Nuraida Kashmin
If you want to add multiple cells together in Excel, then you can go through this article. There are a few quick ways to add multiple cells in Excel. Today we are going to learn how to use them with numerous techniques and formulas.
Download Practice Workbook
7 Ways to Add Multiple Cells in Excel
Here, we have a list of some names of employees of a company and their corresponding working days with their salaries. Using this dataset we will demonstrate different easy methods to add multiple cells in Excel.
For creating this article, we have used Microsoft Excel 365 version. However, you can use any other version at your convenience.
Method-1: Use of AutoSum Feature to Add Multiple Cells in Excel
The easiest way to add multiple cells is using the AutoSum feature. By clicking on the AutoSum option, Excel will automatically add multiple cells by entering the SUM function. Suppose we have a table of people’s names and their working days.
Now we are going to add the total working days.
Steps:
- First, click Cell C10, and then go to the Home tab.
- Next from the Editing group of commands, click on AutoSum.
- In Cell C10, a formula appears and points to the cells we want to add.
- Hit ENTER.
Now the result is showing in the required cell.
- You can also do this by going to the Formulas tab >> AutoSum.
And, the final result will be the same as the previous one.
Read More: How to Add Specific Cells in Excel (5 Simple Ways)
Method-2: Applying Algebraic Formula to Add Multiple Cells in Excel
Let’s say we have a worksheet. It contains all the employee’s salaries. Now we are going to add simply all the salary cells to get the total salary in Cell C10.
Steps:
- First, select Cell C10 and type the Equal (=) sign.
- Click on the first cell to add and type the Plus (+) sign.
- Now click on the second cell and repeat till all the cells add up.
- Press ENTER.
Then the total amount will show up in Cell C10.
Read More: How to Sum Selected Cells in Excel (4 Easy Methods)
Method-3: Utilizing SUM Function to Add Multiple Cells in Excel
The SUM function is one of the most simple ways to add multiple cells in Excel easily. Here, we will utilize this function to get the total salaries of the employees.
Steps:
- Type “=SUM(“ in Cell C10.
- Now select the range of cells that you want to add up.
You can also select each cell separated by commas.
- Press ENTER.
Finally, you will have the total salary in cell C10.
Read More: Shortcut for Sum in Excel (2 Quick Tricks)
Similar Readings
- Sum by Font Color in Excel (2 Effective Ways)
- [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
- How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)
- Sum Cells in Excel: Continuous, Random, With Criteria, etc.
- How to Sum Multiple Rows and Columns in Excel
Method-4: Add up Multiple Cells with Condition Implementing SUMIF Function
To add up the cells that meet certain criteria we can use the SUMIF function. Let’s say we have a worksheet with some random names of employees, their sales quantity, and the sales amount. Now we are going to add up the sales amount where the quantity is less than a certain number 5 and the sales amount that is also less than a certain value of $6,000.00.
Steps:
- Type the following formula in cell C10.
=SUMIF(C5:C9,"<5",D5:D9)Here the SUMIF function goes through the C column. If the criteria match then it will add up the matched data of column D.
As we can see Mark, Hank, and Rock have quantities smaller than 5 and so their corresponding sales amount will be added up here.
- Press ENTER.
In this way, you will get the total sales values for your given criteria.
- Now, write down the following formula in cell D10.
=SUMIF(D5:D9,"<6000")Here the SUMIF function goes through column D to find the values which are smaller than 6000 and then adds them up.
- Hit ENTER.
Later, you will have the following result.
Read More: Excel Sum If a Cell Contains Criteria (5 Examples)
Method-5: Add Multiple Cells Together Containing Texts
Here, we will add up the cells of the First Name column with the cells of the Last Name column to form the full names in the Full Name column. Instead of adding numbers like in the previous sections here, we will add up the texts together. The ways of doing this job are stated in the following 3 sections.
5.1. Applying CONCATENATE Function
In this section, we will add up the texts with a space between them using the CONCATENATE function.
Steps:
- Type the following formula in cell D5.
=CONCATENATE(B5," ",C5)Here, B5 is the first name, ” ” is for a space, and C5 is the last name.
- Press ENTER.
- Drag down the Fill Handle tool.
In this way, you will get all of the full names of the employees.
5.2. Applying TEXTJOIN Function
Here, we will utilize the TEXTJOIN function to get the combined texts.
Steps:
- Type the following formula in cell D5 and press ENTER.
=TEXTJOIN(" ",TRUE,B5,C5)Here, ” ” is the separator of each text, TRUE is for ignoring blank cells, and B5 and C5 are the texts.
- Drag down the Fill Handle tool.
Finally, you will have the combined texts in the Full Name column.
5.3. Utilizing Ampersand Operator
In this section, we will get the full names by combining the two parts of the names using the Ampersand operator.
Steps:
- Type the following formula in cell D5 and press ENTER.
=B5&" "&C5- Drag down the Fill Handle tool.
Eventually, you will get the full names of the employees after the combination of the two parts of the names with a space.
Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
Method-6: Add a Constant Number to Multiple Cells Simultaneously
We are going to add the value in cell E5 in multiple cells of the Salary column.
Steps:
- Select Cell E5 and copy it by pressing CTRL+C.
- Now select the cells where you want to add the copied values and then right-click.
- Click on the arrow symbol beside Paste Special and then click on Paste Special again to see more options.
Afterward, the Paste Special dialog box will appear.
- From the Paste section select Values and in the Operation section select Add.
- Click OK.
You can also open up this dialog box by pressing CTRL+ALT+V.
As a result, we can see that 100 has been added to all of the salaries. But, the formatting has vanished.
- After applying the Currency format again we will get the following outlook.
Read More: How to Add Numbers in Excel (5 Easy Ways)
Method-7: Adding Values of Multiple Cells for a Dynamic Range
Here, we will add up the values of the Salary column in such a way that if we add an extra cell in this column then the value will be automatically added up in the final result. To do this we will use the combination of the SUM, OFFSET, and COUNTA functions.
Steps:
- Type the following formula in Cell E5.
=SUM(OFFSET($C$5,0,0,COUNTA(C:C)-1))Here, $C$5 is the starting cell of the range, the following 2 zeros are indicating that the cell reference will not move by any row or column number. Finally, COUNTA(C: C)-1 is indicating the height number of the range which will be the number of rows having texts or numbers.
Later, the OFFSET function will return us the dynamic range and the SUM function will add up them.
- Press ENTER.
You will get $31,000.00 for the remaining salaries.
If we add an extra row for the salary of an employee named Richard, then we can see the total value has been automatically changed to $39,900.00.
Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)
Practice Section
To practice by yourself, we have created a Practice section on the right side of each sheet.
Conclusion
In this article, we have discussed different ways to add multiple cells in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.
