How to count cells in certain rows in Excel. Sum in excel using sum and sumif functions

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 unreasonably think that this program provides the only opportunity - to "draw" beautiful plates. Far from it, here are some functions and automatic solutions that can be done with 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. Let's fill in some data. Task: calculate the total amount of revenue for the week.

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

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

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

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

Applying Results Preview

The simplest course of action in this spreadsheet is to simply view the results. To do this, there is no need to delve into the complexity of the process of writing formulas or searching for the necessary functions in the variety of possible ones.

We have a table with data and it is enough to select the required 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 calculation and the sum of all values ​​in the selected range (column). Which is what we needed.

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

Using the AutoSum function

Let's use the same table as an example. How to add the entire column of data in Excel and write the result in a separate cell. The program offers us an automatic option for 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 required range with data. We find at the top, on the Excel toolbar, in the "Editing" group, the "AutoSum" button, click and get the result. The result of the function calculation will automatically be written to a free cell below our column.

This button allows you to automatically substitute the formula in an empty cell.
If there is more than one column for summarizing data, then you can either repeat the operation described above, or use another useful function of Excel tables - the Autofill Marker.

Field autocomplete marker

To use this function, you need to make the cell active with the formula we need (in our case, with the amount of revenue for the week), hover the cursor in the lower right corner of the cell, which will be converted into an autofill marker. Next, holding down the left mouse button, drag the cursor to as many cells as you need to substitute the formula.

Applying a simple Excel formula

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

The main condition for writing a formula is the "=" sign before the mathematical operation.

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

By inserting this construction into a 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 construction:

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

For this procedure we need:

• Define a cell for the future result in our data table.
• In this cell we write the sign =.
• Next, we left-click on the first cell of 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 addition sign + and click on the second cell.
• By analogy, we add all the necessary cells of 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 separately, then the work on the document will be delayed indefinitely. Especially if these are large tables with accounting calculations. To save time and effort, you can use the above autocomplete token feature.

The positive aspect of this method relative to the above is the moment of manual selection of the required cell for the result.

SUM function

A more convenient and professional way, as in Excel, to add the sum in a column is to use the built-in functions. For our example, we need to refer to the mathematical function SUM. Unlike the previous method, the entry syntax is more simplified:

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

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

How to add a column in Excel using this function:

• Select the result cell.
• Click on the "Insert Function" icon fx, which is located next to the formula bar.
• In the section "Frequently used", or "Mathematical" we find the SUM.

The function window will open, which usually has two fields for a range of cells « Number 1" and "Number 2". For simple operations, one field is sufficient. We put the cursor in the "Number 1" field and select the range of numbers in the table with the usual selection using the mouse. Cells with data will automatically be 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, you can add the summation function for the remaining ranges of numbers in our table. However, if you take advantage of the knowledge gained and combine different options for working with data tables, you can save a lot of time.

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

Sum cells is a basic function in the Excel spreadsheet program. When working with a large amount of information, it may be necessary to perform a mathematical operation with certain data. However, selecting information manually or using the "IF" function in a separate column, and then summing up these cells is quite painstaking, and also takes a lot of time. But if you need to select data by several conditions? In the program, all these actions can be combined into one and not waste precious time. In this article, you will learn how to sum cells by conditions.

If you want to know detailed instructions, you can read an article about this on our portal.

This function can be used when processing large numbers that just need to be added without separating cells according to some criteria. Literally, as "+" in the calculator. To perform a normal addition, you need:

1. Select the cell you need by clicking on it once with the left mouse button.

   

2. Select "fx" and in the list find the action "SUM", this can be done in the category "Full alphabetical list" or in "Math".

   

   

   

3. Click OK and the Function Arguments window will appear. In it, you can add cell values ​​​​or drive in additional numbers.

   Important! Be aware that the program will ignore boolean or text values.

4. Click OK.

   

When summing numbers with one condition, use the "SUMIF" function

For this:

1. Select the cell where you want to display the result. Click on it once with the left mouse button.

   

2. Click on the "fx" ("Insert Function") button, which is located in front of the formula bar.

   

3. The "Function Wizard" will appear. Select "SUMIF", this can be done in the categories: "Full alphabetical list" or in "Math".

   

4. The Function Arguments pop-up menu appears.

   

5. Fill in the range and criteria. Suppose, in our case, we need to find out how much Vasechkin sold the goods for. Select the cells with sellers, click on the first one with a big plus and, without releasing, bring it to the last cell in the range.

   

6. In the line "Criterion" we will enter "Vasechkin" (you can write both with quotes and not, the program will set them automatically).

   

   Note! You can also enter a mathematical expression.

7. We mark the summation range from the "Sum" column.

   

8. Click OK. The result of the action will appear in the previously selected cell.

   

   

On a note! This function can also be entered manually using the basic notation: "=SUMIF(x), where x is the range, criterion, and summation range, which are listed with ";". For example, "=SUMIF(A1:A2,"Condition",B1:B2)".

However, if you need to select information according to several different criteria?

If you want to learn and also learn the basics of working in Excel, you can read an article about this on our portal.

SUMIFS function

This function works the same as "SUMIF", but gives more options in setting the parameters of a mathematical operation. To apply this feature, you need to follow these steps:

1. Check the box.

   

2. Click on "fx" or "Insert Function".

   

3. A window will appear, where from the list select the "SUMIFS" function.

   

4. Click on "OK" or double-click on "SUMIFS".

   

5. The Function Arguments menu appears.

   

   Important! Please note that unlike "SUMMESLI", in this window, the summation range is first set, and then the conditions. You can also enter up to 127 conditions.

6. Fill in the condition ranges and the conditions themselves.

   

   

7. Click OK.

   

   Note! You can find more detailed instructions in this article a little higher..

8. Suppose we need to find out how much Vasechkin sold apples for. We have only 2 conditions - the seller must be Vasechkin, and the goods must be apples. In our case, the function arguments will look like this.

   

   

This function can also be entered manually, however, it will be quite difficult because there are many conditions. In order for the program to work correctly, and you do not lose time trying to find an error in a long entry, we recommend that you still use the action via "fx".

You can also find visual instructions in the video.

Video - Sum by Condition in Excel, SUMIFS Function

The simplest and most demanded operation of the office program - excel (excel) - the summation of data. How to calculate the amount in Excel - this task becomes relevant in 2 cases:

• initial values ​​are more than 2;
• the initial data are multivalued (2.35476).

Excel (Microsoft Excel) is designed for all kinds of operations with numerical data in a table presented on a computer monitor as a notebook sheet in a box. Each cell is called a cell. By hovering over it and clicking the left mouse button, you can write down the first original number using the computer keyboard.

In order to add several numbers, you need to write them with "+" signs, and put the "=" sign in front of the first number (for example, \u003d 7 + 3 + 4). After pressing the enter key, the result (14) will appear.

Addition of 2 prime numbers is performed verbally or on a calculator. If there are a lot of initial parameters, for example, when they accumulate over a period of time, then determining the amount without a computer becomes a difficult routine task.

Data arranged vertically is called a "column", while data arranged horizontally is called a "row". You can fill them out using the "copy" option on the toolbar of the "main" section.

When entering a number by keyboard or copying from other sources, spreadsheets are created for subsequent calculations.

The dimensions of spreadsheets are many times larger than the volume of any notebook, so all operations are carried out automatically. The execution of formulas is determined by standard computer mouse manipulations.

One-click summation

Move the cursor to the empty cell following the last value in the column (row) and click (click) with the left mouse button. Then, on the toolbar in the “home” section, find the sum icon ∑ and click on it with the cursor.

The smart program will outline the range with a running dotted line for visual control of the correctness of the calculations, and the formula bar will show the numbers of the analyzed cells.

If you notice a data entry error, just press the esc key to cancel the addition. By pressing the enter key on the keyboard, we get the desired result.

If there are few values ​​and they are located on one page of the table, then it is enough to move the cursor over them with the left mouse button pressed so that the lower panel shows their sum.

When analyzing source data, it often becomes necessary to add them up, choosing from different columns or rows, for example, the maximum numbers.

All operations are performed by hovering over the sections of the control panel and cells with data, clicking ("click") with the left mouse button, in the following sequence:

• any empty cell;
• "FORMULAS" section of the control panel;
• "Insert function" section;
• section "Category", select "Mathematical";
• section "Select a function" select "SUM", go to the section "Function arguments";
• table cell with the first term;
• section "Function Arguments", line "Number 2";
• table cell with the second term.

After all the terms are entered, click "OK" in the "Function Arguments" window, having received the final result.

The program allows you to find more complex sums of data combined by a condition (SUM IF).

Such conditions can be a negative value, extreme values, i.e. only negative values ​​or values ​​differing from extrema will be subjected to addition.

You can add the results of intermediate calculations: squares, products, squared differences and the difference of squares - necessary for statistical analysis.

Spreadsheets can contain thousands of raw data, but a simple operation in Excel guarantees a quick calculation of their sum.

When working with tables, it is often necessary to sum up the totals for a specific item. This name can be the name of the counterparty, the surname of the employee, the number of the department, the date, etc. Often these names are the headings of rows, and therefore, in order to calculate the total for each element, it is necessary to sum up the contents of the cells of a particular row. Sometimes the addition of data in rows is performed for other purposes. Let's take a look at the different ways you can do this in Excel.

By and large, there are three main ways to sum values ​​in a row in Excel: using an arithmetic formula, using functions, and autosum. 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, using an arithmetic formula, you can calculate the amount in a line. Let's see how this method works 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.

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, when compared with the options that we will consider below. And if the table has a lot of columns, then the time costs will increase even more.

Method 2: AutoSum

A much faster way to add data in a row is to use autosum.

As you can see, this method allows you to calculate the amount in the line much faster than the previous version. But he 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

To overcome the shortcomings of the two methods described above, you can use the built-in Excel function called SUM.

Operator SUM belongs to the group of Excel mathematical functions. Its task is to sum numbers. The syntax for this function is as follows:

SUM(number1, number2,…)

As you can see, the arguments of this operator are the numbers or addresses of the cells in which they are located. Their number can be up to 255.

Let's see how you can sum the elements in a row using this operator using our table as an example.

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 if you need to sum not one or two lines, but, say, 10, 100 or even 1000? Really for each line it is required to apply the above described actions separately? As it turns out, it's not necessary at all. To do this, you just need to copy the summation formula to other cells in which you plan to display the sum for the rest of the lines. This can be done using a tool called the fill marker.

As you can see, there are three main ways to calculate the sum of values ​​in rows in Excel: arithmetic formula, autosum, and the SUM function. Each of these options has its own advantages and disadvantages. The most intuitive way is to use a formula, the fastest option is AutoSum, and the most versatile is to use the SUM operator. In addition, using the fill handle, you can perform mass summation of values ​​across rows, performed in one of the three ways that were listed above.

Today we will look at:

- a program for creating spreadsheets, which is widely used both in organizations and at home, for example, for home bookkeeping. In the process of using Excel, many users are faced with the need to calculate the sum of a column. Of course, you can do it manually, but it's much easier to follow the tips given in the article.

Below we will look at several ways that will help you get the sum of the cells in a column.

Method 1

Just select the cells with your mouse and then click the autosum icon. The total amount will be displayed in an empty cell below.

Method 2

Another variant. Click on the empty cell where you want the amount to be displayed, and then click on the autosum icon.

The program will select the area to be added, and the formula will be displayed in the place where the total amount will be located.

You can easily adjust this formula by changing the values ​​in the cells. For example, we need to calculate the sum not from B4 to B9, but from B4 to B6. When finished typing, press the Enter key.

Method 3

But what if the cells required for summation are not one after another, but scattered? This task is a little more difficult, but it is also easy to handle.

For clarity, the cells that we need to add, we will highlight in color. Now click the mouse cursor on the cell where the amount will be located and put a sign = . It is with this sign that any formula begins.

Now click on the first cell to be summed. In our case, this is B7. The cell will appear in the formula. Now put up a sign + and add the rest of the cells in the same way. You will end up with something like the screenshot below.

Press the Enter key to execute the formula. The system will display the number with the sum of the cells.