How To Add In Excel Down, Between Columns And Between Sheets

Table of Contents[Show]
How To Add In Excel Down, Between Columns And Between Sheets


How do I add up in Excel? This time we will learn how to add up in Excel, both the addition of data down, the addition between columns and the addition between different sheets with Excel formulas. As well as excel amount formula tips.

This discussion on how to addition in Excel will complement the previous discussion on Basic Arithmetic Operations in Excel. 

At the end of the discussion, there are tips on how to choose references between excel sheets that are rarely known.

How to Add in Excel

Adding data in Excel is one of the basic skills that must be mastered by everyone who is learning Microsoft Excel.

In general, to add multiple values ​​in Excel, you can use the plus (+) operator.

For example, to add the numbers 10, 20 and 30 the sum formula is:


=10 + 20 + 30

 

If each of these numbers is in cells A1, B1 and C1, the formula for the sum is:


=A1 + B1 + C1

 

Adding separately for a large range of data is certainly less efficient. Apart from the + operator, addition in Excel can also be done using the SUM function.

For example, to add up the range A1: A500 the number formula you can use is:


=SUM (A1:A500)

 

If you put the sum just below or next to a range of data to be added, use Shortcut. For example, if you want to add numbers in the range A1: A99, place the cursor on cell A100 then press shortcut Alt + =. After pressing the Shortcut button, Excel will automatically enter the formula in the cell. For data in excel table format, there is another automatic addition which will be explained in the next section. 

Alt + =

Alt + =


=SUM (A1:A99)


The addition method in Excel with these three methods is the most basic way to calculate the amount in Excel.

Next, we will discuss examples of addition in Microsoft Excel.

Sum down Excel Formula

Consider the following data table:

Sum down Excel Formula


1) Excel Addition Formulas

To add up data on sales of goods in each column or each store, as in the example above, there are several ways of adding that you can do.

How to sum in Excel down with the plus operator (+)

The first way to add in excel is to use the plus / add (+) operator. To do the sum in this way enter the following excel formula in cell B8:


=B4+B5+B6+B7


Adding Down Excel Formula Using plus Operator

 

This method is of course less effective if the data we are going to add is large enough. The next alternative is to use excels SUM function.

How to Sum In Excel Down With the SUM Function

The second way to add data down in Excel is to use the SUM function in Microsoft Excel. To add data with the SUM function, write the following excel formula in cell B8:


=SUM (B4:B7)


Sum Down Excel Formula With SUM Function

 
 

In addition to writing the sum formula manually, you can also use Shortcut to enter the sum formula above. The trick is to place the cursor on cell B8 then press Shortcut. Alt + =  Alt + =


For the next step, you don't need to write the formula to other cells next to it, but only need to copy and paste the excel formula into other adjacent cells for Store 2 and Store 3 either manually or by using the excel auto fill feature.

You can learn more about how to use the Excel SUM function in the following sections: Excel SUM formulas.

How to Sum Data in Excel Down with the SUBTOTAL function

The third way to add down on one column of data is to use the Subtotal function like the following excel formula:


=SUBTOTAL (109,B4:B7)

 

Sum Down Excel Formula With SUBTOTAL Function

  

If you intend to use the Subtotal Formula, an easier way is to change the data as a table format. 


If it is formatted as a table, you just have to turn it total row on Tab Design, then select the SUM menu on the corresponding row total row.

Enabling Total Row in Excel Table Format

Consider the following picture:

Add-down Excel formula to table format

 

Then how to add data to the side and between columns in Excel?

2) Addition between Columns Excel Formula

If what is meant between these columns is the addition in one row of data, for example to fill in the Total column in the data table above, then the addition method is more or less the same as the downward addition formula as discussed above.

How to Add Between Columns Using the Plus (+) Operator

If you use the add (+) operator, the excel formula for addition to cell E4 is:


=B4+C4+D4

 

Copy and paste the formula into the other cells below in the total column. Look at the following picture:

Addition Between Columns Excel Formula Using Plus (+) Operator

 

How to sum in Excel is similar to the previous one. The difference is which cell references will be summed.

How to Add Between Columns Using the SUM Formula

If you use the Excel SUM function, the excel formula that you can use to add to cell E4 in the Total column is as follows:


=SUM (B4:D4)


Addition Between Columns Excel Formula With SUM Function

 
 

This method of adding data in Excel with the SUM function is a method commonly used by every Microsoft Excel user.

How to Add Between Columns Using the SUBTOTAL Formula

If you use the subtotal function to add sideways or between columns, the excel formula that you can use for cell E4 column totals is:


=SUBTOTAL (9,B4:D4)

 

or 


=SUBTOTAL (109,B4:D4)

 

Addition Between Columns Excel Formula With SUBTOTAL Function

  

3)Addition Formulas Between Sheets

The previous examples of addition apply if the cells we are counting are close together or are in the same data (Range).

What if the data we are going to add are in different data ranges?

Addition of Different Tables on the Same Sheet

Before discussing addition between sheets, we first discuss addition in different ranges on the same sheet. For example, we want to add up different tables such as for the example of the following image:

Excel Formulas Addition of Different Tables on the Same Sheet

To fill in cell B13 which is the number of Book sales at Store 1 for the January and February periods the excel formula that can be used is:


=B4+H4

 

Or with the SUM function:


=SUM(B4,H4)

 

On the Sum Line (line 17) In addition to using the addition formula described earlier, you can also use the following addition formula:


=SUM(B4:B7:H4:H7)

 

Addition of Different Tables on Different Sheets

How to add data for different sheets?

For example, we have the following January sales data on Sheet 1:

Goods Sales Data for January

 

Then we also have February sales data on sheet 2 as below:

Goods Sales Data for February

 

On sheet 3 we will add up the January and February data. How do you do it?

On Sheet 3, cell E4 write the following excel formula:


=Sheet1!E4+Sheet2!E4

 

Addition Excel Formulas Between Sheets Using Plus (+) Operator

 

Or if you use the SUM function, the excel formula is as follows:


=SUM(Sheet1!E4,Sheet2!E4)

 

SUM function, the excel formula is as follows:


Yes, the method of adding between sheets or adding different sheets is actually relatively the same as the previous addition excel formulas.

It's just that for cell references or ranges, we need to add the Sheet Name and Exclamation Mark (!) Before the cell address or range that we will add up as in the example above.

The addition formula for the different sheets above is certainly not the only formula that you can use.

For addition of different sheets but in the same data range, you can also use the addition formula as follows:


=SUM(Sheet1!B4:B7,Sheet2!B4:B7)

 

Excel Formula Addition of Multiple Sheets in the Same Cell Reference or Range 15

With the above formula, Excel will add up each cell in the range B4: B7 on Sheet1 and sheet2.

If you are lazy to manually write the excel formula above, how to determine the reference for the addition excel formula as above is:

1) Select each sheet that we will add using the keys Ctrl on the keyboard, hold down then follow the sheet name selection. For consecutive sheets you can also use the Shift key and select the final sheet you added.

2) After the sheet is selected / selected, then determine the range of data that will be added.

 

Before I end the discussion, in the case of addition between these sheets you can also use the following excel formula writing trick:

Suppose you have an excel file with Sheet names: January, February, March, April, May, June, July, August, September, October, November, December .

To add up Cell A1 on each sheet with the letter M prefix (March and May) write the following formula:


=SUM('M*'!A1)

 

Then press Enter.

Taraa ... excel will automatically determine the addition range you mean.

Another example if you have an excel file with sheet names is the names of the months ending in 2003, 2007, 2010, 2013, 2016 or any other year .


Then you only want to add up to cell A1 on each sheet that ends in 2010, so try writing the following formula:


=SUM('*2010'!A1)

 

Then press Enter. So easy isn't it?

I have finished our discussion on how to add to the bottom, between columns and between sheets. If you find it useful, don't hesitate to press the share button below, maybe one of your friends needs this tutorial.

Leave a Comment