Unable to calculate amount in excel. Calculating the sum of a column in Microsoft Excel

What is MS Excel? For many users, this is a program where there are tables in which you can put some information. But in fact, MS Excel has the greatest possibilities, which most people never think about, and even cannot imagine.

In this article, we will consider one of the most important functions in MS Excel - the sum of cells. The developers have worked hard to create this program. They made sure that the amount could be calculated not only in one way, but in several. That is, you can choose the most simple and convenient way for yourself, and use it in the future.

Consider the input options in more detail, from the simplest to the more complex.

How to calculate sum in MS Excel?

Finding the amount using the “+” sign is the easiest way and, accordingly, there is no need to look for and invent anything here, since a plus is also a plus in Africa.

Suppose that we have three cells filled: A1, A2, A3 and we need to find their sum.

For this:

1. click on any free cell, in this case A4
2. print the sign "="
3. select cell A1
4. print the "+" sign
5. select cell A2
6. print the "+" sign again
7. select cell A3
8. press the Enter button

This option is good if you need to calculate a small number of values. What if there are dozens of them?

How MS Excel calculate the sum of a column (or row)?

For this case, there are two ways: the "Summarize" button (AutoSum) and the "=SUM ()" function.

AutoSum is a function with which you can add many cells at once in a few seconds.

Consider step by step:

1. select a free cell, in this case A5 (it is best to select the cell under the numbers that we will add so that the program itself will try to recognize the cells necessary for summing)

2. call the "Summarize" function, for this a special button on the toolbar is used

3. if Excel did not select the necessary cells on its own, then you can do it manually by holding the left mouse button on the first cell and, without releasing the mouse button, drag to the last one, selecting the entire range

4. press the Enter button to get the result

In turn, “=SUM ()” (or SUM in the English version) is the simplest function that is not inferior to AutoSum, in which the range of cells that we will sum is indicated in brackets. The range can be entered both manually and selected with the mouse. There are two options to use this feature.

Option 1. Manual entry.

1. select a free cell

2. enter the “=” sign in the formula bar

3. print the function SUM (A1: A4) (or SUM (A1: A4) in the English version), where A1: A4 is the range of cells used

4. press the Enter button

By the way, if you use the second method in this version, then this formula can be edited, like any other, and this is done right in the formula bar. For example, you need to multiply the resulting value by two. To do this, we print "*2" in the formula line and get the following formula: \u003d SUM (A1: A4) * 2.

Option 2: Enter using the function wizard.

1. select a free cell in which the summation will take place
2. click on the button to call the Function Wizard: fx
3. in the dialog box, select the category of the desired function, in this case "Mathematical"
4. in the list, select the function "SUM" (or SUM) and click OK
5. select the desired range of cells (B3:B20) and press the Enter key

Again, the question arises: how to read the sum of different cells in MS Excel?

In this case, we can use both a simple “+” sign and the “=SUM ()” function. Well, if in the first case everything is very simple and does not require explanation (Fig. 10), then the second one needs to be dealt with a little.

Let's say you need to add individual cells from a table. What will we do for this:

1. as before, select a free cell and call the Function Wizard

2. select the SUM function

3. in brackets, one by one, separating the numbers from each other with a semicolon, select either the necessary cells or the necessary ranges of cells

4. press the Enter key

You can watch an even more detailed description in the video: http://www.youtube.com/watch?v=nk04P2Jkgwk
However, with large amounts of information, it is possible that we do not need to summarize all the values, but only those that meet certain conditions.

How to calculate sum with condition in MS Excel?

For this option, the function "=SUMIF()" will be used. There are of course other functions, but this function is more suitable.

The general form of this function is SUMIF(range, criterion, sum_range), where "range" is the data range where the condition will be searched, "criteria" is the specific condition that will be tested in this range, and "sum_range" is the range from which values ​​are selected that match the given condition.

Let's look at an example step by step. Let's say we have a ready-made table and we need to determine the total cost of all products of the same name.

For this:

1. below the finished table, repeat the line with the names of the columns and enter each product name, but only without repetitions
2. select the cell in which the summation will take place (D15) and call the Function Wizard
3. in the dialog box, enter the parameters of our function: range - B2:B11 - product names, criterion - B15 - specific name of interest, sum_range - F2:F11 - cost that will be summed up.
4. press OK and get the result

Naturally, as in previous cases, the formula can be written manually.

You can see a more detailed description in the video:

Here we have considered the main functions for summation. Good luck with using MS Excel. Thank you for your attention!

This article will cover how to calculate the sum in a column with an indeterminate number of rows, and how to calculate the sum in a column excluding the header or excluding the first few rows.

How to calculate the sum in a column using the SUM function

SUM(B2:B8)

How to calculate the sum in a column in Excel - Calculate the sum in a column

There is a second way, like in Excel calculate column sum automatically . To do this, select the last empty cell in the column in which you want to calculate the sum. On the tab " HOME"In the group" Editing", Click " Autosum"And the key" Enter». After that, the "SUM" formula will be automatically entered in the cell:

How to calculate the sum in a column in Excel - In a table, calculate the sum of a column using AutoSum

How to calculate the sum in a column with an indeterminate number of rows

If you need calculate sum in column which has a variable number of rows (i.e. new cells can be added and existing ones can be deleted at any time), you can calculate the sum of the entire column, specifying a column reference but not specifying a lower or upper bound, for example:

Note! Under no circumstances should the formula " Column sum ' to the column you want calculate the amount, because that will create a circular reference to the cell (i.e. an infinite recursive summation) and your SUM formula will return 0.

How to calculate sum in a column in Excel - Calculate sum in a column using column reference

How to calculate the sum in a column excluding the header or excluding the first few rows

Typically, column references are used in the SUM formula to calculate sum in column , ignoring the header as shown in the image above. But in some cases, the column header may contain some numeric value. Or, for example, you want to exclude the first few rows with numbers that are irrelevant to the data.

Unfortunately, Microsoft Excel does not accept a mixed SUM formula with an explicit lower bound but no upper bound, such as =SUM(B2:B). If you need calculate sum in column excluding the first few lines, you can use one of the next few solutions.

1. Count column sum, and then subtract the cells you don't want to include in the grand total (cells B1-B3 in this example):

SUM(B:B) - SUM(B1:B3)

How to calculate the sum in a column in Excel - Calculate the sum in a column excluding the first few rows

1. Keeping sheet size limits in mind, you can specify an upper bound for the SUM formula based on the maximum number of rows in your version of Excel.

In Excel 2007, Excel 2010, Excel 2013, and Excel 2016:

SUM(B2:B1048576)

SUM(B2:B655366)

In this article, we have looked at different ways how to calculate sum in a column in excel, and now you can choose any of them depending on the situation.

If you work in Excel: build graphs, create tables, various reports, etc., then you do not need to look for a calculator to add certain data. The sum of numbers in Excel is calculated in a couple of mouse clicks, and this can be done in various ways.

Using selection

The simplest one will allow you to add up all the numbers that will be in the selected area. Highlight the desired range and look at the status bar. This will say "Average" for all values, "Number" of selected cells, and "Sum" of values. You can select empty blocks and those where there is text, since only numbers are added.

If your table has a line "Totals" - this method will not work. Of course, you can enter a number there, but if the data for the table changes, then you need to remember to change them in the "Totals" block. This is not very convenient, and in Excel you can use other methods that will automatically recalculate the result in the block.

Using autosum

You can sum the numbers in a column using the "Autosum" button. To do this, select an empty cell in the column, just below the values. Then go to the Formulas tab and click on AutoSum. Excel will automatically select the top blocks, up to the first empty one. Press "Enter" to calculate everything.

You can also first select the blocks in the column, taking into account both empty and with text - they simply will not be taken into account in the calculation, and then click "Autosum". The result will appear in the first empty cell of the highlighted column.

Using the formula

You can make the calculation we need in Excel using a familiar mathematical formula. Put "=" in the desired cell, then select all the necessary ones with the mouse. Don't forget to put a plus sign between them. Then press "Enter".

The most convenient way to calculate is to use the SUM function. Put "=" in the cell, then type "SUM", open the bracket "(" and select the desired range. Put ")" and press "Enter".

You can also write a function directly in the formula bar.

If you specify a range of blocks as an argument for the function, which, in addition to numbers, contain both text and empty blocks, the function will return the correct result. This is because only cells with numbers will be taken into account for the calculation.

Hello!

Many who do not use Excel - do not even imagine what opportunities this program gives! Just think: automatically add values ​​from one formula to another, search for the desired lines in the text, add by condition, etc. - in general, in fact, a mini-programming language for solving "narrow" tasks (to be honest, for a long time I myself did not consider Excel as a program, and almost did not use it) ...

In this article, I want to show several examples of how you can quickly solve everyday office tasks: add something, subtract something, calculate the sum (including with a condition), substitute values ​​from one table to another, etc. That is, this article will be something like a mini guide on learning the most necessary things for work (more precisely, to start using Excel and feel the full power of this product!).

It is possible that if you had read a similar article 15-17 years ago, I myself would have started using Excel much faster (and would have saved a lot of my time for solving "simple" (note: as I understand now) tasks)...

Note: All screenshots below are from Excel 2016 (as the newest to date).

Many novice users, after starting Excel, ask one strange question: "well, where is the table?". Meanwhile, all the cells that you see after starting the program are one big table!

Now to the main thing: in any cell there can be text, some number, or a formula. For example, the screenshot below shows one illustrative example:

• left: Cell (A1) contains the prime number "6". Note that when you select this cell, the formula bar (Fx) just shows the number "6".
• right : in cell (C1) it also looks like a simple number "6", but if you select this cell, you will see the formula "=3+3" - this is an important feature in Excel!

Just a number (on the left) and a calculated formula (on the right)

The bottom line is that Excel can count like a calculator if you select some cell, and then write a formula, for example "=3+5+8" (without quotes). You do not need to write the result - Excel will calculate it and display it in the cell (as in cell C1 in the example above)!

But you can write in formulas and add not just numbers, but also numbers already calculated in other cells. In the screenshot below, in cell A1 and B1, the numbers are 5 and 6, respectively. In cell D1, I want to get their sum - you can write the formula in two ways:

• first: "=5+6" (not very convenient, imagine that in cell A1 - we also have a number calculated according to some other formula and it changes. You won’t substitute a number instead of 5 every time?!);
• the second: "=A1+B1" - and this is the ideal option, just add the value of cells A1 and B1 (regardless of what numbers they contain!)

Add cells that already have numbers

Extending a formula to other cells

In the example above, we added two numbers in column A and B in the first row. But then we have 6 lines, and most often in real problems you need to add numbers in each line! To do this, you can:

1. on line 2 write the formula "=A2+B2" , on line 3 - "=A3+B3", etc. (this is long and tedious, this option is never used);
2. select cell D1 (which already has a formula), then move the mouse pointer to the right corner of the cell so that a black cross appears (see screenshot below). Then hold down the left button and stretch the formula to the entire column. Convenient and fast! (Note: You can also use Ctrl+C and Ctrl+V combinations for formulas (copy and paste respectively)).

By the way, pay attention to the fact that Excel itself has substituted formulas in each line. That is, if you now select a cell, say D2, you will see the formula "=A2+B2" (i.e. Excel automatically fills in the formulas and returns the result immediately) .

How to set a constant (a cell that will not change when copying a formula)

Quite often it is required in formulas (when you copy them) that some value does not change. Let's say a simple task: convert prices in dollars into rubles. The cost of the ruble is set in one cell, in my example below it is G2.

Next, in cell E2, the formula "=D2*G2" is written and we get the result. Only now, if we stretch the formula, as we did before, we will not see the result in other lines, because Excel in line 3 will put the formula "D3*G3", in the 4th line: "D4*G4", etc. G2 must remain G2 everywhere...

To do this - just change the cell E2 - the formula will look like "=D2*$G$2". Those. dollar sign $ - allows you to set a cell that will not change when you copy the formula (i.e. get a constant, example below)...

How to calculate the sum (SUM and SUMIFS formulas)

You can, of course, write formulas manually by typing "=A1+B1+C1" and so on. But Excel has faster and more convenient tools.

One of the easiest ways to add up all selected cells is to use the option autosums (Excel will write the formula itself and paste it into the cell).

1. first select the cells (see screenshot below);
2. then open the section "Formulas";
3. the next step is to press the "Autosum" button. Under the cells you selected, the result of the addition will appear;
4. if you select the cell with the result (in my case, this is the cell E8) - then you will see the formula "=SUM(E2:E7)" .
5. thus writing the formula "=SUM(xx)", where instead of xx put (or select) any cells, you can read a wide variety of ranges of cells, columns, rows...

Quite often, when working, it is required not just the sum of the entire column, but the sum of certain rows (i.e. selectively). Suppose a simple task: you need to get the amount of profit from some worker (exaggerated, of course, but the example is more than real).

I will use only 7 rows in my table (for clarity), the real table can be much larger. Suppose we need to calculate all the profit that "Sasha" made. What the formula will look like:

1. "=SUMIFS(F2:F7 ;A2:A7 ;"Sasha") " - (note: pay attention to the quotation marks for the condition - they should be like in the screenshot below, and not as it is written on my blog now). Also note that Excel, when driving in the beginning of a formula (for example, "SUM ..."), itself prompts and substitutes possible options - and there are hundreds of formulas in Excel!;
2. F2:F7 - this is the range over which the numbers from the cells will be added (summed up);
3. A2:A7 is the column by which our condition will be checked;
4. "Sasha" is a condition, those rows in which "Sasha" will be in column A will be added (pay attention to the indicative screenshot below).

Note: there can be several conditions and you can check them in different columns.

How to count the number of rows (with one, two or more conditions)

Quite a typical task: to calculate not the sum in the cells, but the number of rows that satisfy some condition. Well, for example, how many times the name "Sasha" occurs in the table below (see screenshot). Obviously, 2 times (but this is because the table is too small and taken as a good example). How can this be calculated as a formula? Formula:

"=COUNTIF(A2:A7 ,A2 )" - where:

• A2:A7- the range in which lines will be checked and counted;
• A2- a condition is set (note that you could write a condition like "Sasha", or you can just specify a cell).

The result is shown on the right side of the screenshot below.

Now imagine a more extended task: you need to count the rows where the name "Sasha" occurs, and where the number "6" will be in the AND column. Looking ahead, I will say that there is only one such line (screen with an example below).

The formula will look like:

=COUNTIFS(A2:A7 ;A2 ;B2:B7 ;"6") (note: pay attention to the quotes - they should be like in the screenshot below, and not like mine), where:

A2:A7 ;A2- the first range and search condition (similar to the example above);

B2:B7 ;"6"- the second range and search condition (note that the condition can be specified in different ways: either specify a cell, or simply write text / number in quotes).

How to calculate the percentage of the amount

It's also a very common question that I often come across. In general, as far as I can imagine, it occurs most often - due to the fact that people are confused and do not know what percentage is looking for (and in general, they do not understand the topic of interest well (although I myself am not a great mathematician, and yet ...)).

The simplest way, in which it is simply impossible to get confused, is to use the "square" rule, or proportion. The whole essence is shown on the screen below: if you have a total amount, let's say in my example this number is 3060 - cell F8 (i.e. this is 100% profit, and "Sasha" made some part of it, you need to find which ... ).

In proportion, the formula will look like this: =F10*G8/F8(i.e. cross by cross: first we multiply two known numbers diagonally, and then divide by the remaining third number). In principle, using this rule, it is almost impossible to get lost in percentages.

Actually, this concludes this article. I'm not afraid to say that having mastered everything that is written above (and only "heels" of formulas are given here) - you will be able to learn Excel on your own, flip through help, watch, experiment, and analyze. I will say even more, everything that I described above will cover many tasks, and will allow you to solve the most common ones that you often rack your brains over (if you don’t know the capabilities of Excel), and you don’t know how to do it faster ...

Often, when working with tables in Microsoft Excel, you need to calculate the amount for a separate column of data. For example, in this way you can calculate the total value of an indicator for several days, if the lines of the table are days, or the total cost of several types of goods. Let's find out the different ways you can add column data in Microsoft Excel.

The easiest way to view the total amount of data, including the data in the cells of a column, is to simply select them with the cursor by pressing the left mouse button. At the same time, the total sum of the selected cells will be reflected in the status bar.

But, this number will not be entered into the table, or stored elsewhere, and is given to the user simply for information.

AutoSum

If you want to not only find out the sum of the column data, but also enter it into a table in a separate cell, then it is most convenient to use the autosum function.

In order to use autosum, select the cell that is located under the desired column, and click on the "Autosum" button located on the ribbon in the "Home" tab.

Instead of clicking a button on the ribbon, you can also press the keyboard shortcut ALT+=.

Microsoft Excel automatically recognizes the cells of the column filled with data for the calculation, and displays the finished result in the specified cell.

To see the finished result, just press the Enter button on the keyboard.

If, for some reason, you think that the autosum did not take into account all the cells that you need, or, on the contrary, you need to calculate the sum not in all the cells of the column, then you can manually define the range of values. To do this, select the desired range of cells in the column, and grab the first empty cell below it. Then, click on the same "Autosum" button.

As you can see, the amount is displayed in an empty cell, which is located under the column.

AutoSum for Multiple Columns

The sum for several columns at the same time can be calculated, as well as for one column. That is, select the cells under these columns, and click on the "AutoSum" button.

But what if the columns whose cells need to be summed are not located next to each other? In this case, hold down the Enter button, and select the empty cells located under the desired columns. Then, click on the "Autosum" button, or type the key combination ALT + =.

As an alternative, you can select the entire range in those cells in which you need to find out the sum, as well as empty cells below them, and then click on the autosum button.

As you can see, the sum of all the indicated columns has been calculated.

Manual totalization

Also, it is possible to manually sum the cells in a table column. This method, of course, is not as convenient as counting through the autosum, but on the other hand, it allows you to display these amounts not only in the cells located under the column, but also in any other cell located on the sheet. If desired, the amount calculated in this way can even be displayed on another sheet of an Excel book. In addition, in this way, you can calculate the sum of the cells not of the entire column, but only of those that you select yourself. At the same time, it is not at all necessary that these cells border on each other.

We click on any cell in which you want to display the amount, and put the “=” sign in it. Then, one by one, click on those cells of the column that you want to summarize. After entering each next cell, you need to press the "+" key. The input formula is displayed in the cell you selected and in the formula bar.

When you have entered the addresses of all cells, to display the result of the sum, press the Enter button.

So, we have looked at various ways to calculate the sum of data in columns in Microsoft Excel. As you can see, there are both more convenient, but less flexible methods, as well as options that require more time, but at the same time allow you to select specific cells for calculation. Which method to use depends on the specific tasks.