How to make a total in Excel. Percentages in Excel. How to calculate percentages of the total amount in Excel

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:

Number of values ​​in the status bar

You can select the indicators displayed in a row by right-clicking on it.

Customizing the Status Bar

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 carried out 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 ;

Counting the number of lines

NUMCOLUMN(array) – similar to the previous one, but counts the number of columns in the array

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: =ROW(array)*COLUMNNUMBER(array) .

How to count empty cells in Excel
Counting empty cells

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
Counting numerical values

If you need to determine the number of cells containing values, use the COUNTA(value1,value2,...) function. 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.

Count non-blank cells 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 is 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

Counting cells with condition

If you need to take into account several conditions, use the COUNTIFS(array1,criteria1,[array2],[criteria1]…) function. 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.

Counting values ​​according to several conditions How to determine the most common number

To find the number that is most often found in an array, there is an Excel function 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.

Frequently occurring number

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 (eg) 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!

No matter how users feel about changes regarding office programs, the fact remains: they were, are and will be, and therefore you will have to adapt to their rather changeable appearance. As you know, we're talking about O latest versions MS Office, which are very different from their predecessors. Let's just see if appearance the developers limited themselves this time.

Russian has finally become one of the languages ​​represented on the list, so there is no need to worry about translation difficulties. In addition, the programmers, for once, kept their promise and rolled out a full-fledged x64 version to the public. However, they honestly warn that if there is no need to work with files Excel sizes 2GB and more, it is advisable to use the obsolete 32-bit version.

Let's leave the lyrics aside. Today we will learn how to calculate the amount in Excel. Yes, for experienced users there is nothing complicated about this, but still most beginners have never encountered this.

Calculation process

To be fair, it is worth noting that calculating total values ​​is one of the simplest operations with formulas that this spreadsheet editor has. Let's look at the most basic example.

Let's assume that work is done in a column in the range A4 to A9. Before calculating the sum of these values ​​in Excel, you only need to select all these cells and one empty cell 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. Let's assume that the required values ​​are in the same cells. But you may need the result you are looking for 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 amount button. After this, use the left mouse button to indicate the range that contains the data you need. The desired value will appear in the resulting cell. If necessary, the range of selected cells can be changed.

More complex option

Everything that we discussed above falls into the category of those banal information that is given to students in their first computer science lessons. But what if the desired value must be obtained based on data that is in completely different ranges?

Before you calculate the amount in Excel in such conditions, you will have to do a little more. First, place the cursor in the cell that will contain the total amount. We put the “=” sign there, and then use the left mouse button to select the cell whose value should be summed with other numbers.

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

"Quick sum"

How can you calculate in Excel if you just need to quickly look at the total value of several cells, without collecting complex expressions for this? Well, the creators of this software package(it’s hard to call it a utility) they also foresaw this scenario.

At the very bottom of the worksheet there is a line with the word “Done”. Click on it right key mouse, and in the dropped context menu select the “Amount” item. After this, you need to select the required range of cells with the left mouse button while holding down Ctrl button on keyboard.

Where to look for the answer?

Having selected the cells and released the key on the keyboard, pay attention to the lower right corner of the working window: there will be a line “Amount =”, which indicates the resulting value.

As you can see, there is no need to use a calculator for intermediate calculations, since Excel easily beats it in all respects. Yes, yes, since not everyone can calculate the amount in Excel, experienced accountants often use this technique.

Another way

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

In order for you to get a formula in Excel (the amount in words will not work without it), you must first find out where to enter it. Just below the toolbar (or below the ribbon in newer versions) there is a long box. It has an Fx icon at the beginning, so you can't go wrong.

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 this, write “=” in the formula field, and then write those cells that contain the data you require, separating them with a “+” sign.

For example: =A1+B2+C3. When you finish typing, press the ubiquitous Enter. The number you are looking for will appear in the field you select. Since in Excel it is often necessary to calculate the amount in this particular way, you must remember about it.

Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Excel Mobile Excel Starter 2010 Less

The SUM function is one of the mathematical and trigonometric functions that adds values. You can add individual values, cell ranges, cell references, or all three.

=SUM(A2:A10)

=SUM(A2:A10,C2:C10)

Syntax

SUM(number1;[number2];…)

Method =1+2 or =A+B. You can enter =1+2+3 or =A1+B1+C2 and get completely accurate results, but this method is unreliable for a number of reasons.

Typos. Let's say you're trying to type a lot large values this type:

• =14598,93+65437,90+78496,23

Then try to check that the entries you entered are correct. It is much easier to put these values ​​in separate cells and use the SUM formula. Additionally, you can format the values ​​once they are in cells, making them much easier to read once they are in a formula.

Let's say you use a formula like this:

• =A1+B1+C1 or =A1+A2+A3

The formula may break if there are any non-numeric (text) values ​​in the reference cells that return #VALUE! error. The sums function will ignore text values and give the sum of only numeric values.

When you delete a row or column, the formulas are not updated to exclude the deleted values, so the #REF! error is returned. The SUM function, in turn, updates automatically.

If you insert a row or column, the formula will not update to the added row, where the sums function will automatically update (as long as you are not outside the range referenced in the formula). This is especially important if you are expecting the formula to be updated and it didn't help, leaving unfinished results that can't be caught.

SUM function - individual cells or ranges

Using a formula like this:

• =SUM(A1,A2,A3,B1,B2,B3)

Inserting or deleting rows in a referenced range is similarly error-prone, for the same reasons. It's much better to use separate ranges, for example:

=SUM(A1:A3,B1:B3)

This formula will update as rows are added and removed.

Common problems

Problem	Possible reason
The SUM function displays the characters ##### instead of the result.	Check the width of the columns. # # # # # usually means the column is too narrow to display the formula result.
The SUM function displays the formula itself as text, not the result.	Make sure the cell is not formatted as text. Select the cell or range you want and then press Ctrl+1 Format Cells and then open the Number tab and select required format. If the cell was formatted as text and will not change after you change the format, you may need to press F2 > Enter to change the format.
The SUM function is not updated.	Make sure the calculation is set to "automatic". On the Formula tab, select Calculation Options. You can also use the F9 key to force a worksheet to be calculated.
Some values ​​are not added.
The error value #NAME? is displayed instead of the expected result.	This usually means that the formula contains an error: for example, instead of =SUM(A1:A10), you entered =SUM(A1:A10).
The SUM function displays an integer number when it should display a decimal number.	Check if display is selected decimal numbers in cell format. Select the appropriate cell or range and click Ctrl keys+ 1 to open the Format Cells dialog box, then click the Number tab and select the format you want, specifying required quantity decimal places.

If you work in Excel program: build graphs, create tables, various reports, etc., then in order to add up certain data you do not need to look for a calculator. 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. Select the required range and look at the status bar. This will show the "Average" of all values, the "Count" of selected cells, and the "Sum" of values. You can select empty blocks and those with text, since only numbers are added.

If your table has a “Totals” row, 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 it in the “Results” block. This is not entirely 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 immediately below the values. Then go to the Formulas tab and click 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 ones and those with text - they simply will not be taken into account in the calculation, and then click “AutoSum”. The result appears in the first empty cell of the selected column.

Using the formula

You can make the calculation we need in Excel using the familiar one mathematical formula. Place “=” in the required cell, then use the mouse to select all the ones you need. Don't forget to put a plus sign between them. Then press "Enter".

Most convenient way for calculation is to use the SUM function. Put "=" in the cell, then type "SUM", open the bracket "(" and highlight 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 to the function, which, in addition to numbers, contain both text and empty blocks, the function will return correct result. This is because only cells with numbers will be taken into account for the calculation.

Rate this article:

The Excel spreadsheet editor is rightfully considered the best. Its functionality includes many tools that are designed to make the user's work easier. Enumerating them all would not be enough for any article, but talking about some of their features is another matter.

This article will discuss how to calculate percentages in Excel. Several methods will be demonstrated in which the necessary calculations can be easily made.

Formula

Now let's look at its use. As an example, we will use the numbers 9 and 17, where 9 is a number and 17 is the total.

Presets

However, you should not immediately enter the formula; initially, before calculating percentages in Excel, you need to change the value to “Percent” on the toolbar, in the “Number” group; you can see a more precise location of this option in the photo below.

Example

Now let's move directly to the example. In order to find out the percentage of the number 9 from 17, you must do the following:

Place the cursor in the desired cell.

Enter an expression. IN in this case this is "=9/17" (due to the fact that we have completed pre-setting, multiplying by 100 is not necessary).

Press ENTER.

After this, a percentage value will appear in the cell - this means that everything was done correctly. Now you know, but that’s not all that I would like to tell you in the article.

We derive the percentage from the table value

Entering the necessary values ​​for calculating the percentage yourself is stupid if you have already compiled a table in Excel; it would be much more reasonable to use the program’s capabilities that will speed up this process.

So, to find out the percentage of total number using the data in the table, you must:

Place the cursor in the right place (it is better to place it next to the number whose percentage you want to know).

Enter an equal sign (=).

Left-click (LMB) on the number.

Place a fraction sign (/).

LMB on the total amount.

Click ENTER key.

After this, the percentage of the amount will be calculated in the place where you entered the formula. Now you know the second way to calculate percentages in Excel.

Quick calculation of all data

Calculating percentages in Excel can be significantly faster if you need to calculate many rows in one column. To do this, you need to do the following:

Enter the formula into the desired cell, exactly as described in the previous section.

Before the second value that comes after the division sign (/), put the symbol ($), then press the right arrow and put this sign again, the result should be something like this: “=E3/$E$8”.

Press the ENTER key to perform the calculation.

Stretch the selection along the entire length of the column with values ​​by holding LMB on the lower right corner of the selection.

After this, all calculations will be made. As you can see, this is very convenient and much faster than entering all the formulas manually.