How to calculate the amount in a column in excel. Excel: you need to calculate the amount in columns

Excel is a very cool thing, if you understand how to work with this program. The application has so many functions that the head goes round and round, and the average user does not put into practice practically anything. Take, for example, the question of how to calculate the amount in Excel. Many do not know this and continue to count on the calculator, and then they drive in the table ready-made values.

This method has the right to life, but why put yourself in danger of making a mistake when you can automate the counting process by forcing the machine to add, subtract, divide, multiply, calculate interest and perform other operations? In this case, the result does not have to be double-checked - the computer has no chance to make a mistake when it comes to simple mathematics.

Simple addition

If all the numbers are located in one column, then to get their sum you can use the simplest way, which does not require the use of formulas.

There are several ways to select cells. If they are nearby, then just hold the left mouse button near the first value and drag the selection to the last variable. You can also hold down the Shift key and press the down arrow on the keyboard, sequentially selecting the cells that are lower.

If the variables are scattered across the sheet, then hold down the Ctrl key and left-click on the values \u200b\u200byou want to add. The amount will change with each click, adding the number that was just highlighted.

Excel can also be used as a calculator. For example, if you need to add several numbers, enter the following formula in any cell:

Be sure to put the “\u003d” sign at the beginning, otherwise the program will not understand that you need to not only add the numbers, but also display the value on the screen. You do not need to put spaces: all characters are written together.

AutoSum function

If the result of addition needs to be reflected on the same sheet, use the special button on the top panel. By default, it is set to the Sum function, but you can use it to perform other calculations. If you select the data that is written in one column, and then click on this button, the desired amount will appear under the column.

If the numbers are clogged on different sides of the sheet, and you need to collect them to summarize and show the result in a certain place, then use the following algorithm of actions:

Then you can use two methods: hold down the Ctrl key and use the left button to select all the variables sequentially or simply enter the addresses of the cells in the formula through the “+” sign. The choice of method depends on how many numbers you need to add. When all cell addresses are driven into the formula, press Enter. Instead of a large number of characters, the sum of the variables added to the formula is displayed.

Using the formula

If it is understood that there is a special cell on the sheet for the sum of the number, then the result can be displayed in it using the simple formula that students learn in computer science lessons.

When choosing numbers, you can use two types of permutations:

• \u003d SUM (B1: B10) - the addition of all values \u200b\u200bthat are fixed in the range B1-B10.
• \u003d SUM (B1; B10) - addition of values \u200b\u200bin cells B1 and B10.

In order not to confuse the separator characters, minimize the window and select cells manually. Fill in the first number, the second, and so on, and then click OK. As a result of the operation, the result of the addition will be displayed in the selected cell.

The Microsoft Excel program is not only a large table, but also an ultramodern calculator with many features and capabilities. In this lesson we will learn how to use it for its intended purpose.

All calculations in Excel are called formulas, and they all start with an equal sign (\u003d).

For example, I want to calculate the sum of 3 + 2. If I click on any cell and type 3 + 2 inside, and then press the Enter button on the keyboard, then nothing will be counted - 3 + 2 will be written in the cell. But if I type \u003d 3 + 2 and press Enter, then everything will be calculated and the result will be shown.

Remember two rules:

All calculations in Excel begin with \u003d

After you enter the formula, you need to press the Enter button on the keyboard

And now about the signs with which we will count. They are also called arithmetic operators:

Addition

Subtraction

* multiplication

/ division. There is also a stick with an inclination in the other direction. So, it does not suit us.

^ exponentiation. For example, 3 ^ 2 read as three squared (to the second power).

% percent. If we put this sign after any number, then it is divided by 100. For example, 5% will turn out 0.05.
  With this sign you can calculate interest. If we need to calculate five percent out of twenty, then the formula will look like this: \u003d 20 * 5%

All these signs are on the keyboard either at the top (above the letters, along with the numbers), or to the right (in a separate block of buttons).

To print characters at the top of the keyboard, press and hold the button with the inscription Shift and with it press the button with the desired sign.

Now let's try to count. Suppose we need to add the number 122596 to the number 14830. To do this, left-click on any cell. As I said, all calculations in Excel begin with a “\u003d” sign. So, in the cell you need to print \u003d 122596 + 14830

And in order to get an answer, you need to press the Enter button on the keyboard. After that, the cell will no longer have a formula, but a result.

And now pay attention to such a top field in the Excel program:

This is the "Line of Formulas". We need it in order to check and modify our formulas.

For example, click on the cell in which we just calculated the amount.

And look at the line of formulas. It will show how exactly we got this value.

That is, in the line of formulas we see not the number itself, but the formula by which this number was obtained.

Try in some other cell to type the number 5 and press Enter on the keyboard. Then click on this cell and look at the formula bar.

Since we simply printed this number, and did not calculate it using a formula, it will be in the line of formulas.

How to count

But, as a rule, this method of "counting" is not used so often. There is a more advanced option.

Suppose there is such a table:

I'll start with the first position of "Cheese". I click in cell D2 and print the equal sign.

Then I click on cell B2, since I need to multiply its value by C2.

I print the multiplication sign *.

Now I click on cell C2.

And finally, I press the Enter button on the keyboard. All! In cell D2, the desired result was obtained.

By clicking on this cell (D2) and looking at the formula bar, you can see how this value turned out.

I will explain using the same table as an example. Now the number 213 is entered in cell B2. Delete it, print another number and press Enter.

Let's look into the cell with the sum of D2.

The result has changed. This happened because the value in B2 changed. After all, our formula is as follows: \u003d B2 * C2

This means that Microsoft Excel multiplies the contents of cell B2 by the contents of cell C2, whatever it is. Draw your own conclusions :)

Try to compose the same table and calculate the sum in the remaining cells (D3, D4, D5).

Excel is a convenient environment for working with tabular data. The application has many functions and commands that allow you to perform any operations with numeric and textual cell data. Even in order to calculate the amount in this program, there are several different ways.

Suppose you have several cells containing numbers, and you would like to add them, as in the example in the screenshot. You can add values \u200b\u200bMonday through Friday (cell B2 to B6) by entering the following formula in any other free cell in the spreadsheet. This formula is as follows: "\u003d cell1 + cell2 + cell3".

Step 1.  Select a free cell in which the sum of numbers will be displayed. In the screenshot, this is cell B8. Now enter the formula in it, for example, "\u003d B2 + B3 + B4 + B5 + B6".

Step 2  Press enter. The formula will be automatically saved in the cell, and the calculation result will be displayed as shown in the example below.

Note!If one or more cells is empty or contains text, not a number, Excel will simply ignore these values \u200b\u200bwhen calculating the result.

Also note that in Excel it is not essential to enter all data in uppercase. If you enter the formula in lower case, as shown below - Excel will automatically convert each letter to uppercase when you press "Enter":

The main advantage of using cell references instead of specific numbers is that the formula will always reflect the current value of the sum of these cells. Each time you change one of the values \u200b\u200bin cells B2, B3, B4, B5 or B6, their sum in cell B8 will change accordingly.

On a note!  This method is suitable only for small tables and if cells with numbers are scattered throughout the table. However, using it to calculate the amount within a row, column, or any other range of cells is extremely inconvenient, in which case the built-in functions are best and faster to handle.

Method 2. Excel Sum Function

The SUM () function is a much more efficient way to add cells. It can be used to sum the numbers of individual cells. However, it also allows you to add a range by simply specifying the first and last cells to be added. This is especially convenient because you can use it to write a simple formula containing hundreds or thousands of cells.

The syntax of the function is as follows - "\u003d SUM (Number1, Number2, ... Number255)", where:

1. Number1 - (required) the first value to be added. This argument may contain actual data, a specific number, or a link to a cell with data to be added.
2. Number2, Number3, ... Number255 - (optional) additional values, summed up to 255.

Step 1.  Select a blank cell in the table or outside. Now you need to insert the formula. To do this, click on the formula symbol in the toolbar and select “SUM” in the window that appears.

Step 2  Now you need to adjust the range of cells that will be summed up by our formula.

Step 3  Click on the OK button. Now in the cell you have selected the amount will be displayed.

You can register this function yourself, without using a dialog box. To do this, you will again have to select an empty cell and manually enter the formula with the necessary range into it, for example, we calculate the sum of both columns with the sum and quantity for LED lamps “Pear”.

So, choosing a free cell, we enter the following into it: “\u003d Sum (R29C4: R33C5)”, where the first character set is before the colon, this is the upper left cell of the range, and the second is the lower right one. After entering the formula, click on the “Enter” button.

Note!The name or cell of your choice is displayed in the upper left corner, to the left of the formula bar. You can’t do without it if you are dealing with complex, huge tables.

In addition to manual entry, you can also use a keyboard shortcut, which, when pressed simultaneously, will automatically register this function in the selected cell. The combination of keys to enter the SUM function: Alt + \u003d (equal sign). Example:

There are several varieties of this feature. They are used for different purposes and have different syntax. You can familiarize yourself with them in the table below.

Method 3. AutoSUM Function

For those who prefer to use a mouse rather than a keyboard, you can also use the AutoSum command to enter the SUM function. It is located in the formula section of the toolbar.

“AutoSum” is a function in a spreadsheet program that inserts a formula into a selected cell and sums the numbers in the column above it. It sets the range of cells by searching for numerical data over the selected cell. This is the fastest way to calculate the amount in a column.

Step 1.  Select an empty cell under the data column.

Step 2  Now, in the "Formulas" section, click on the "AutoSum" command.

Step 3  The function will automatically select the entire column with numbers and compose a form for calculating their sum. The formula “SUM” appears in the active cell with a link to the cells above. At this point, press Enter.

Now the sum of the entire column will be reflected in the cell of your table. As you can see, this is a very quick and easy way.

Note! If the program did not select all the cells you need, you can manually expand the selection frame in the third step. All cells are not included automatically; you can expand the frame to select them.

All methods discussed above have their advantages and features. Having mastered at least one of the last two, you most likely will not have any problems finding the sum of the numbers contained in the table cells.

Video - How to calculate the sum of numbers in a column or row

No matter how users relate to changes related to office programs, the fact remains: they were, are and will be, and therefore they will have to adapt to their rather variable appearance. As you already understood, we are talking about the latest versions of MS Office, which are very different from their predecessors. Let's see if the developers were limited in appearance this time.

The Russian language has finally become one of the ones on the list, so you should not worry about the difficulties of translation. In addition, programmers for some time kept their promise and rolled out a full x64 version to the public. However, they honestly warn that in the absence of the need to work with Excel files of 2 GB or more, it is more desirable to use the obsolete 32-bit version.

Leave the lyrics aside. Today we will learn how to calculate the amount in Excel. Yes, for experienced users this is not difficult, but still most of the newcomers have never come across this.

Calculation process

In fairness, it is worth noting that the calculation of total values \u200b\u200bis one of the simplest operations with formulas, which is only in this table editor. Let's analyze the most elementary example.

Assume that the work is done in a column in the range from A4 to A9. Before calculating the sum of these values \u200b\u200bin Excel, you only need to select all these cells and one empty one below them (that is, the range from A4 to A10), and then click on the sum button. The program will automatically calculate the result and put it in cell A10.

There is another way. Suppose the desired values \u200b\u200bare in the same cells. But you may need the result to be in a cell from a different range (B7, for example). How to achieve this?

First, select cell B7 with the left mouse button, and then click on the sum button. After that, use the left mouse button to indicate the range in which the data you need is contained. The desired value appears in the final cell. If necessary, the range of selected cells can be changed.

More difficult option

Everything that we examined above belongs to the category of the banal information that is given to students in the very first lessons of computer science. But what if the desired value should be obtained on the basis of data that are in completely different ranges?

Before you can calculate the amount in Excel in such conditions, you will have to do a little more. First, put the cursor in the cell in which the total amount will be. We put the “\u003d” sign there, and then select the cell with the left mouse button, the value of which should be summed with other numbers.

After the equal sign, its value will appear (A6, for example). We put the “+” sign after A6, continue to mark the range you need. When done, just press the Enter key: the desired value will appear in the desired cell.

"Quick Amount"

And how in “Excel” to calculate you only need to quickly look at the total value of several cells, without collecting complex expressions for this? Well, the creators of this software package (to call it a utility does not rotate the language) have provided such an option for the development of events.

At the very bottom of the worksheet there is a line with the word “Finish”. We click on it with the right mouse button, and select the “Amount” item in the pop-up context menu. After that, you need to select the required range of cells with the left mouse button while holding down the Ctrl button on the keyboard.

Where to look for an answer?

Having selected the cells and releasing the key on the keyboard, we pay attention to the lower right corner of the working window: there will be a line “Amount \u003d”, in which the resulting value is indicated.

As you can see, you do not need to use a calculator for intermediate calculations, since Excel easily outperforms it in all respects. Yes, yes, since not everyone can calculate the amount in Excel, accountants with experience often use this particular technique.

Another way

If you carefully read our article, you probably drew attention to the number of formulas used in the calculations. Yes, you are right: if you independently enter them in the appropriate field, you can don’t touch the mouse at all. Let's look at how this is implemented in practice.

In order for you to get the formula in “Excel” (the amount in words will not work without it), you first need to find out where to enter it at all. Immediately below the toolbar (or under the ribbon in new versions) there is a long field. At its beginning there is an Fx badge, so you won’t be mistaken.

This is where the required functions are introduced. Suppose you need to know the sum of a number of cells that are evenly distributed across a table. First, select the field in which the equation will be calculated. After that, we write “\u003d” in the formula field, and then we prescribe those cells that contain the data you need, separating them with the “+” sign.

For example: \u003d A1 + B2 + C3. When finished typing, press the ubiquitous Enter. The desired number appears in the field you selected. Since in Excel there is often a need to calculate the amount in this way, you must remember about it.

For those who have not yet understood all the positive aspects of the wonderful Excel program, I advise you to familiarize yourself with the various features of spreadsheets in more detail. Many people unreasonably think that this program provides the only opportunity - to "draw" beautiful tablets. This is far from the case, and here are some features and automatic solutions that can be made using Excel.

Automatic summation of data in cells

Let's make a simple table with several rows and columns. For example, these will be the days of the week and the store’s revenue for each day. Fill in any data. Task: calculate the total revenue for the week.

There are several options for how to add the numbers in a column in Excel:

• AutoSum function.
• Application of a simple formula of mathematical addition.
• SUM function.

In addition to these features, the program provides the ability to preview the results without entering them in any cell.

So, let’s take a closer look at all the options how to add a column with data in Excel.

Apply Results Preview

The easiest way to do this tabular processor is to simply view the results. For this, there is no need to delve into the complexity of the process of writing formulas or finding the necessary functions in the variety of possible.

We have a table with data and it’s enough to select the necessary range and the smart program itself will tell us some results.

We see the average value, we see the number of elements that are involved in the miscalculation and the sum of all the values \u200b\u200bin the selected range (column). Which is what we needed.

The disadvantage of this method is that we can only remember the result and in the future we will not be able to use it.

Application of the “AutoSum” function

We use the same table as an example. As in "Excel" add the entire column with data and write the result in a separate cell. The program offers us an automatic option of finding the sum in the selected range of cells.

To do this, we need our data table. We select, as in the previous time, the necessary range with data. We find on the top, on the Excel toolbar, in the "Editing" group, click the "AutoSum" button and get the result. The result of the calculation of the function is automatically written to a free cell below our column.

This button allows you to automatically substitute a formula in an empty cell.
If there are more than one columns for summing the data, then you can either repeat the operation described above, or use another useful function of the Excel tables - “Autofill marker”.

AutoFill Marker

To use this function, you need to make an active cell with the formula we need (in our case, the weekly revenue), move the cursor to the lower right corner of the cell, which will be converted to an autofill marker. Then, holding the left mouse button, drag the cursor onto as many cells as the formula needs to be substituted.

Applying a simple Excel formula

For users who know the basics of working with this program, there will be no revelation that all mathematical formulas can be written manually in the required cell.

The main condition for writing the formula is the “\u003d” sign before the mathematical operation.

The formula for adding numbers is no different from a conventional calculator:
= 6+2

By inserting this design into the cell, you will immediately see the result, i.e. the number 8.

To view the formula itself, you need to make the cell active and in the formula bar you will see the design:

Based on this, we can consider the main topic of the question - how to add a column in Excel.

For this procedure we will need:

• Define a cell for future result in our data table.
• In this cell we write the \u003d sign.
• Next, we left-click on the first cell in the column and in our cell for the result we will see how the cell address is automatically added (in this example, cell L2).
• We write the mathematical sign of addition + and click on the second cell.
• By analogy, we add all the necessary cells in the column.
• Press Enter.

This Excel formula allows us to add columns without problems, but the method is quite long and not convenient. If you do such manipulations with each column individually, then work on the document will drag on indefinitely. Especially if these are large tables with accounting calculations. To save time and effort, you can use the autofill marker feature described above.

A positive aspect of this method relative to the above is the moment of the presence of manually selecting the required cell for the result.

SUM function

A more convenient and professional way, as in “Excel” to add the amount in a column, is to use the built-in functions. For our example, we need to turn to the mathematical function SUM. Unlike the previous method, the record syntax has a more simplified form:

SUM (x; x1; x2; ... xN;)

A maximum of 255 elements per line is allowed: both numbers and the addresses of the cells in which these numbers are located.

How to add a column in Excel using this function:

• Select the result cell.
• We click on the icon "Insert Function" fx, which is located next to the line of formulas.
• In the “Frequently Used” or “Mathematical” section we find the SUM.

A function window opens, in which there are usually two fields for a range of cells « Number 1 "and" Number 2 ". For simple operations, a single field is sufficient. We put the cursor in the field “Number 1” and with the usual selection with the mouse we select the range of numbers in the table. Data cells are automatically added, just click the OK button in the lower right part of the function panel.

All. The required range is added to the result cell, and the selected function automatically processed it.

Similarly, we can add the summation function for the remaining ranges of numbers in our table. However, if you use the knowledge gained and combine various options for working with data tables, you can save a lot of time.

To do this, just use the familiar autofill marker.
These are the most used and simplest ones that allow us to no longer wonder how to add a column or row or just two different numbers in Excel.