How to make Excel calculate the amount. How to calculate percentages of the total amount in Excel. We derive the percentage from the table value
- select an area of cells;
- use the “AutoSum” button;
- create a simple addition formula;
- using the function.
How to calculate the amount in a column in Excel
Calculate the sum by selecting an area of cells
Firstly, you can find out the sum of any cells with values by simply selecting the cells you need with the left mouse button:
By selecting the cells with numbers, Excel will display the sum of the values in the range you selected in the lower right corner.
In order to select cells that are not adjacent, you should hold down CTRL key and select cells with the left mouse button.
How to calculate the sum in a column using the simple addition formula
Perhaps the simplest and most primitive way of summing data in a column is the simple addition formula. To summarize data:
- left-click on the cell in which you want to get the addition result;
- enter the formula:
In the formula above, indicate the numbers of the cells that you want to sum:
How to calculate the amount in a column using the “AutoSum” button
If you want to calculate the sum of numbers in a column and leave this result in the table, then perhaps the easiest way is to use the “AutoSum” function. It will automatically determine the range of cells required for summation and save the result in the table.
To count the numbers in a column using AutoSum, do the following:
- Click on the first empty cell in the column under the values you want to sum:
- Select the “AutoSum” icon on the toolbar:
- After pressing the button, the system will automatically select the range for summation. If the system has chosen the range incorrectly, you can correct it simply by changing the formula:
- Once you are sure that the range of values for the amount is selected correctly, simply press the Enter key and the system will calculate the amount in the column:
How to calculate the amount in a column using the SUM function in Excel
You can add the values in a column using the function. Most often, a formula is used to create the sum of individual cells in a column or when the cell containing the sum does not need to be located directly below the data column. To calculate the amount using the function, follow these steps:
- Select the cell with the left mouse button and enter the “ ” function, specifying the required range of cells:
- press the “Enter” button and the function will calculate the amount in the specified range.
How to calculate the amount in a column in Excel using a table
To calculate the sum of a data column, you can format the data as a table. To do this:
- Select a range of cells with data and convert them into a table using the “Format as Table” button on the toolbar:
- Once your data is presented in table format, on the “Design” tab in the toolbar, select “Total Row” to add the sum of the columns below the table:
How to calculate the sum in several columns in Excel at the same time
When working with tables, you often have to summarize general results for a specific item. This name can be the name of the counterparty, the employee’s last name, department number, date, etc. Often these names are the titles of rows and therefore, in order to calculate the total for each element, you have to sum up the contents of the cells of a particular row. Sometimes adding data in rows is done for other purposes. Let's take a look various ways how this can be done in Excel.
By and large, you can sum the values in a row in Excel in three main ways: using an arithmetic formula, using functions and autosums. At the same time, these methods can be divided into a number of more specific options.
Method 1: Arithmetic Formula
First of all, let's look at how you can use an arithmetic formula to calculate the amount in a line. Let's see how it works this method on a specific example.
We have a table that shows the revenue of five stores by date. Store names are row names, and dates are column names. We need to calculate the total revenue of the first store for the entire period. To do this, we will have to add up all the cells of the line that relates to this outlet.
![](https://i0.wp.com/lumpics.ru/wp-content/uploads/2017/04/Formula-slozheniya-v-Microsoft-Excel.png)
As you can see, this method is quite simple and intuitive, but it has one significant drawback. It takes a lot of time to implement it, compared to the options that we will consider below. And if there are a lot of columns in the table, then the time costs will increase even more.
Method 2: Autosum
Much more in a fast way Adding data in a row is using an autosum.
![](https://i0.wp.com/lumpics.ru/wp-content/uploads/2017/04/Vyizov-avtosummyi-cherez-vkladku-Glavnaya-v-Microsoft-Excel.png)
As you can see, this method allows you to calculate the amount in a line much faster than the previous option. But it also has a drawback. It consists in the fact that the amount will be displayed only to the right of the selected horizontal range, and not in the place where the user wants.
Method 3: SUM function
The disadvantages of the two methods described above can be overcome by using the built-in Excel functions called SUM.
Operator SUM belongs to the group mathematical functions Excel. Its task is to add numbers. The syntax of this function is as follows:
SUM(number1;number2;…)
As you can see, the arguments of this operator are numbers or addresses of the cells in which they are located. Their number can be up to 255.
Let's see how we can sum the elements in a string using of this operator using our table as an example.
![](https://i0.wp.com/lumpics.ru/wp-content/uploads/2017/04/Perehod-v-Master-funktsiy-v-Microsoft-Excel-14.png)
As you can see, this method is quite flexible and relatively fast. True, it is not intuitive for all users. Therefore, those of them who do not know about its existence from various sources rarely find it in the Excel interface on their own.
Method 4: Bulk summing values in rows
But what to do if you need to sum not one or two lines, but, say, 10, 100 or even 1000? Is it really necessary to apply the above steps separately for each line? As it turns out, this is not at all necessary. To do this, you just need to copy the summation formula into other cells in which you plan to display the sum for the remaining lines. This can be done using a tool called a fill marker.
![](https://i1.wp.com/lumpics.ru/wp-content/uploads/2017/04/Marker-zapolneniya-v-Microsoft-Excel-10.png)
As you can see, in Excel there are three main ways to calculate the sum of values in rows: an arithmetic formula, an autosum and the SUM function. Each of these options has its own advantages and disadvantages. The most intuitively simple way is to use the formula, the most quick option- autosum, and the most universal is the use of the SUM operator. In addition, using the fill handle, you can mass sum values across rows, done in one of the three ways listed above.
Excel spreadsheet processor is an excellent solution for processing various types of data. With its help, you can quickly make calculations with regularly changing data. However, Excel is a rather complex program and therefore many users do not even begin to learn it.
In this article we will talk about how to calculate the amount in Excel. We hope this material will help you become familiar with Excel and learn how to use its basic functions.
Also, the “Auto sum” button is duplicated on the “Formula” tab.
After you highlight a data column and click the Auto Sum button, Excel will automatically generate a formula to calculate the column's sum and insert it into the cell immediately below the data column.
If this arrangement of the column sum does not suit you, then you can specify where you want to place the sum. To do this, you need to select the cell suitable for the sum, click on the “AutoSum” button, and then select the column with the data with the mouse and press the Enter key on the keyboard.
In this case, the column sum will not be located under the data column, but in the table cell you selected.
How to calculate the sum of specific cells in Excel
If you need to calculate the sum of certain cells in Excel, then this can also be done using the Auto Sum function. To do this, you need to select the table cell in which you want to place the amount with the mouse, click on the “Auto Sum” button and select the desired cells while holding down the CTRL key on the keyboard. Once the desired cells are selected, press the Enter key on your keyboard and the amount will be placed in the table cell you selected.
In addition, you can enter a formula to calculate the sum of certain cells manually. To do this, place the cursor where the amount should be, and then enter the formula in the format: =SUM(D3; D5; D7). Where instead of D3, D5 and D7 are the addresses of the cells you need. Please note that cell addresses are entered separated by commas; a comma is not needed after the last cell. After entering the formula, press the Eneter key and the sum will appear in the cell you selected.
If the formula needs to be edited, for example, you need to change the cell addresses, then to do this you need to select the cell with the amount and change the formula in the formula bar.
How to quickly view the amount in Excel
If you need to quickly see what the total will be if you add certain cells, and at the same time you do not need to display the value of the sum in the table, then you can simply select the cells and look down the Excel window. There you can find information about the sum of the selected cells.
The number of selected cells and their average value will also be indicated there.
Date: February 29, 2016 Category:Hello friends. Today we are learning how to count cells in Excel. These functions solve a wide range of problems for representatives of many professions. By performing intermediate calculations, they become the basis for automating your calculations. I know many managers who use counting functions to work with their impressive range of products.
If you just need to know the number of values without using them in calculations, it is convenient to look at the data in the status bar:
![](https://i1.wp.com/officelegko.com/wp-content/uploads/2016/02/kolichestvo-v-stroke-sostoyzniya.png)
You can select the indicators displayed in a row by right-clicking on it.
![](https://i2.wp.com/officelegko.com/wp-content/uploads/2016/02/nastroyka-stroki-sostoyzniya.png)
If you need to use the number of values in further calculations, use the functions described below. For convenience of notation, we will assume that the data array for which the count is being made is . In your formulas, you can use the desired data range instead of the name.
How to count the number of cells in Excel
To count the number of cells in Excel, there are two functions:
- LINE(array) – counts the number of lines in the selected range, regardless of what its cells are filled with. The formula gives results only for a rectangular array of adjacent cells, otherwise ;
![](https://i1.wp.com/officelegko.com/wp-content/uploads/2016/02/kolichestvo-strok.png)
- NUMCOLUMN(array)– similar to the previous one, but counts the number of array columns
Excel doesn't have a function to determine the number of cells in an array, but it can be easily calculated by multiplying the number of rows by the number of columns: =NUMROW(array)*NUMBERCOLUMN(array).
How to count empty cells in Excel
![](https://i1.wp.com/officelegko.com/wp-content/uploads/2016/02/pustoti.png)
The function considers a cell empty if there is nothing written in it, or the formula inside it returns an empty string.
How to count the number of values and numbers in Excel
![](https://i0.wp.com/officelegko.com/wp-content/uploads/2016/02/schet-chisel.png)
If we need to determine the number of cells containing values, we use the function COUNTA(value1,value2,…). Unlike previous function, it will count not only numbers, but also any combination of symbols. If the cell is not empty, it will be counted. If there is a formula in the cell that returns zero or an empty string, the function will also include it in its result.
![](https://i0.wp.com/officelegko.com/wp-content/uploads/2016/02/schet-znacheniy.png)
How to count cells with a condition in Microsoft Excel
- Array – a range of cells among which the count is performed. You can only specify a rectangular range of adjacent cells;
- Criterion – the condition by which selection occurs. Write text conditions and numeric conditions with comparison signs in quotation marks. We write equal numbers without quotes. For example:
- “>0” – count cells with numbers greater than zero
- “Excel” – count cells containing the word “Excel”
- 12 – counting cells with the number 12
![](https://i1.wp.com/officelegko.com/wp-content/uploads/2016/02/schet-s-usloviem.png)
If you need to take into account several conditions, use the function COUNTIFS(array1,criteria1,[array2],[criteria1]…). The function can contain up to 127 array-criteria pairs.
If you use different arrays in one such function, they should all contain the same number of rows and columns.
![](https://i0.wp.com/officelegko.com/wp-content/uploads/2016/02/schet-s-usloviyami.png)
How to determine the most common number
To find the number that occurs most often in an array, there is a function in Excel MODE(number1;number2;…). The result of its execution will be the same number that occurs most often. To determine their number, you can use a combination of summation formulas and array formulas.
If there are several such numbers, the one that appears earlier in the list will be displayed. The function only works with numeric data.
![](https://i2.wp.com/officelegko.com/wp-content/uploads/2016/02/funktsiya-moda.png)
This is, perhaps, the entire list of functions that I want to present to you as part of this post. They are quite enough to solve the most popular cell counting problems. Combine them with other features (for example) to get maximum results.
In the next article we will study. Go read it, even if you are sure you know everything about it. I think there will be something there just for you!
Hello!
Many who don’t use Excel don’t even imagine what opportunities this program provides! Just think: fold in automatic mode values from one formula to another, search for the required lines in the text, add according to a condition, etc. - in general, essentially a mini-programming language for solving “narrow” problems (to be honest, I myself spent a long time Excel time I didn’t consider it as a program, and almost didn’t use it)...
In this article I want to show several examples of how you can quickly solve everyday office tasks: adding something, subtracting something, calculating a sum (including with a condition), substituting values from one table to another, etc. That is, this article will be something like a mini guide on learning what you need for work (more precisely, to start using Excel and feel the full power of this product!).
It is possible that if I had read a similar article 15-17 years ago, I myself would have been much more started faster use Excel (and would save a lot of my time solving "simple" (note: as I understand it now) tasks)...
Note: All screenshots below are from Excel programs 2016 (as the newest to date).
Many novice users, after launching 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: any cell can contain text, some number, or a formula. For example, the screenshot below shows one illustrative example:
- left: cell (A1) contains the prime number "6". Please note that when you select this cell, the formula bar (Fx) simply shows the number "6".
- on the right: in cell (C1) there is also 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 point is that Excel can calculate like a calculator if you select some cell and then write a formula, for example "=3+5+8" (without quotes). You don’t need to write the result - Excel will calculate it itself 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 that have already been calculated in other cells. In the screenshot below, in cells A1 and B1 there are numbers 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 - our number is also calculated according to some other formula and it changes. You won’t substitute a new number instead of 5 every time?!);
- second: "=A1+B1" - and here it is ideal option, we simply add the values of cells A1 and B1 (despite even what numbers are in them!)
Adding cells that already contain numbers
Spreading a formula to other cells
In the example above, we added the two numbers in column A and B in the first row. But we have 6 lines, and most often in real problems you need to add numbers in each line! To do this, you can:
- in line 2 write the formula "=A2+B2", in line 3 - "=A3+B3", etc. (this is long and tedious, this option is never used);
- 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 pinch left button and stretch the formula to the entire column. Convenient and fast! (Note: you can also use the combinations Ctrl+C and Ctrl+V for formulas (copy and paste, respectively)).
By the way, pay attention to the fact that Excel itself inserted formulas into each line. That is, if you now select a cell, say D2, you will see the formula "=A2+B2" (i.e. Excel automatically substitutes formulas and immediately produces the result) .
How to set a constant (a cell that will not change when you copy the 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 to rubles. The value of the ruble is specified in one cell, in my example below it is G2.
Next, in cell E2, write the formula “=D2*G2” and get the result. But if we stretch the formula, as we did before, we won’t see the result in other lines, because Excel will put the formula “D3*G3” in line 3, “D4*G4” in line 4, etc. We need G2 to remain G2 everywhere...
To do this, simply change cell E2 - the formula will look like "=D2*$G$2". Those. dollar sign $ - allows you to specify a cell that will not change when you copy the formula (i.e. we get a constant, example below)...
How to calculate the amount (formulas SUM and SUMIFS)
You can, of course, compose formulas in manual mode, typing "=A1+B1+C1" etc. But Excel has faster and more convenient tools.
One of the most simple ways to add all selected cells is to use the option autosums (Excel will write the formula itself and insert it into the cell).
- First, select the cells (see screenshot below);
- Next, open the “Formulas” section;
- The next step is to click the "AutoSum" button. The result of the addition will appear under the cells you selected;
- if you select the cell with the result (in my case it is the cell E8) - then you will see the formula "=SUM(E2:E7)" .
- thus, writing the formula "=SUM(xx)", where instead xx put (or select) any cells, you can read a wide variety of ranges of cells, columns, rows...
Quite often when working, you need not just the sum of the entire column, but the sum of certain rows (i.e. selectively). Let's assume 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), but a real table can be much larger. Suppose we need to calculate all the profits that “Sasha” made. What the formula will look like:
- "=SUMIFS(F2:F7 ;A2:A7 ;"Sasha") " - (note: pay attention to the quotation marks for the condition - they should be as in the screenshot below, and not as I have written on my blog now). Also note that Excel, when entering the beginning of a formula (for example, “SUM..."), itself prompts and substitutes possible options- and there are hundreds of formulas in Excel!;
- F2:F7 is the range over which the numbers from the cells will be added (summed);
- A2:A7 is the column against which our condition will be checked;
- “Sasha” is a condition, those rows in which “Sasha” is in column A will be added (pay attention to the indicative screenshot below).
Note: there can be several conditions and they can be checked using different columns.
How to count the number of lines (with one, two or more conditions)
A fairly typical task: to count not the sum in the cells, but the number of rows that satisfy some condition. Well, for example, how many times the name “Sasha” appears in the table below (see screenshot). Obviously, 2 times (but this is because the table is too small and taken as an illustrative example). How to calculate this with a formula? Formula:
"=COUNTIF(A2:A7,A2)" - Where:
- A2:A7- the range in which rows will be checked and counted;
- A2- a condition is set (note that you could write a condition like “Sasha”, or you can simply specify a cell).
The result is shown on the right side of the screen below.
Now imagine a more advanced task: you need to count the lines where the name “Sasha” appears, and where in the AND column the number “6” will appear. Looking ahead, I will say that there is only one such line (screenshot with an example below).
The formula will look like:
=COUNTIFS(A2:A7 ;A2 ;B2:B7 ;"6") (note: pay attention to the quotation marks - 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 set in different ways: either specify a cell, or simply write text/number in quotes).
How to calculate the percentage of the amount
This is also a fairly common question that I often encounter. In general, as far as I can imagine, it arises most often - due to the fact that people are confused and do not know what they are looking for a percentage of (and in general, they do not understand the topic of percentages well (although I myself am not a great mathematician, and still ... )).
The simplest way, in which it is simply impossible to get confused, is to use the “square” rule, or proportions. The whole point is shown in the screenshot below: if you have a total amount, let’s say in my example this number is 3060 - cell F8 (i.e. it is 100% profit, and some part of it was made by “Sasha”), 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 confused in percentages.
Actually, this is where I conclude this article. I’m not afraid to say that having mastered everything that is written above (and only “five” formulas are given here), you will then be able to independently learn Excel, leaf through the help, watch, experiment, and analyze. I’ll say even more, everything that I described above will cover many tasks, and will allow you to solve the most common ones, which you often puzzle over (if you don’t know the capabilities of Excel), and don’t know how to do it faster...