How to calculate the amount in a row in Excel. 3 Ways to Calculate the Sum of Numbers in an Excel Row

Working with tabular information, users often need to calculate the amount of an indicator. Often these indicators are the names of the lines by which it is necessary to sum up all the information in the cells. From the article you will learn all the methods that allow you to implement this procedure.

Sum values ​​in a row

You can reproduce the process of summing values ​​in a row using the following methods:

• arithmetic formula;
• auto summation;
• various functions.

Each of these methods is subdivided into additional methods. Let’s deal with them in more detail.

Method 1: arithmetic formula

First, let’s find out how, using an arithmetic formula, it is possible to sum up in a row. Let’s analyze everything with a specific example. Let’s say we have a table that shows the revenue of 5 stores on certain dates. The names of outlets are the names of the lines. Dates are column names.

Purpose: to calculate the total amount of income of the first outlet for all time. To achieve this goal, it is necessary to add all the cells of the row related to this store. The walkthrough looks like this:

1. We select the cell where the result will be reflected in the future. Enter the symbol “=” in the cell. We press LMB on the very first cell in this line containing numerical indicators. We notice that after clicking the cell coordinates were displayed in the cell for calculating the result. Enter the “+” symbol and click on the next cell in the row. We continue to alternate the symbol “+” with the coordinates of the cells of the row of the first outlet. As a result, we get the formula: =B3+C3+D3+E3+F3+G3+H3.

2. After carrying out all the manipulations, press “Enter”.
3. Ready! The result was displayed in the cell in which we entered the formula for calculating the amount.

Pay attention! As you can see, this method is clear and easy, but it has one nasty drawback. The implementation of this method takes a lot of time. Let’s consider faster variants of summation.

Method 2: AutoSum

Using autosum is a method that is much faster than the one discussed above. The walkthrough looks like this:

1. Using the pressed LMB, we select all the cells of the first row that have numeric data. We move to the “Home” section, located at the top of the spreadsheet interface. We find the block of commands “Editing” and click on the element called “Editing”.
   

   

Recommendation! An alternative option is to go to the “Formulas” section and click on the “AutoSum” button located in the “Function Library” block. The third option is to use the key combination “Alt” + “=” after selecting the cell.

2. Regardless of which option you applied, a numerical value appeared to the right of the selected cells. This number is the sum of the row scores.

As you can see, this method performs summation in a line much faster than the above. The main drawback is that the result is displayed only to the right of the selected range. In order for the result to be displayed in any chosen place, it is necessary to use other methods.

Method 3: SUM function

Using the integrated spreadsheet function called SUM does not have the disadvantages of the previously discussed methods. SUM is a mathematical function. The task of the operator is the summation of numerical values. General view of the operator: =SUM(number1,number2,…).

Important! Arguments to this function can be either numeric values ​​or cell coordinates. The maximum number of arguments is 255.

The step-by-step tutorial looks like this:

1. We make a selection of any empty cell on the worksheet. In it we will display the result of the summation. It is worth noting that it can even be located on a separate worksheet of the document. After making the selection, click on the “Insert Function” button, located next to the line for entering formulas.

2. A small window called “Function Wizard” was displayed on the screen. Expand the list next to the inscription “Category:” and select the element “Mathematical”. A little lower in the list “Select a function:” we find the SUM operator and click on it. After carrying out all the manipulations, click on the “OK” button located at the bottom of the window.

3. A window called “Function Arguments” appeared on the display. In the empty field “Number1” enter the address of the line, the values ​​in which you want to add. To carry out this procedure, we put the pointer in this line, and then, using the LMB, we select the entire range with numerical values. After carrying out all the manipulations, click on “OK”.

4. Ready! The result of the summation was displayed in the initially selected cell.

SUM function not working

Sometimes it happens that the SUM operator does not work. The main causes of the malfunction:

• incorrect number format (text) when importing data;
• the presence of hidden characters and spaces in cells with numerical values.

It is worth noting! Numeric values ​​are always right-justified and textual information is always left-justified.

How to find the sum of the largest (smallest) values

Let’s figure out how to calculate the sum of the smallest or largest values. For example, we need to sum three minimum or three maximum values.

The GREATEST operator allows you to return the maximum score from the selected data. The 2nd argument specifies which metric to return. In our specific example, the formula looks like this: =СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3})).

The search for the smallest value works in the same way, only the SMALL function is used instead of the GREATEST operator. The formula looks like this: =СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3})).

Stretching summation formula/function to other rows

We found out how the total amount is calculated for the cells in one line. Let’s figure out how to implement the summation procedure over all rows of the table. Writing formulas by hand and inserting the SUM operator are long and inefficient ways. The optimal solution is to stretch the function or formula to the desired number of lines. The walkthrough looks like this:

1. We calculate the amount using one of the above methods. Move the mouse pointer to the lower right frame of the cell with the displayed result. The cursor will take the form of a small dark plus sign. Hold LMB and drag the formula to the very bottom of the plate.

2. Ready! We have summarized the results for all titles. We have achieved this result due to the fact that when copying the formula, the addresses are shifted. The offset of coordinates is due to the fact that addresses are relative.

3. For the 3rd line, the formula looks like: =B3+C3+D3+E3+F3+G3+H3.

How to calculate the sum of each Nth row.

On a specific example, we will analyze how to calculate the sum of each Nth row. For example, we have a table that reflects the daily profit of an outlet for a certain period of time.

Task: to calculate the weekly profit for each week. The SUM operator allows you to sum data not only in a range, but also in an array. Here it is necessary to use the auxiliary operator OFFSET. The OFFSET operator specifies a number of arguments:

1. First point. Cell C2 is entered as an absolute reference.
2. Number of steps down.
3. Number of steps to the right.
4. The number of steps down.
5. The number of columns in the array. Hitting the last point of the array of indicators.

We end up with the following formula for the first week: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). As a result, the sum operator will sum all five numeric values.

3-D sum, or working with multiple sheets of an Excel workbook

To count numbers from the same range shape across a number of worksheets, a special syntax called “3D reference” must be used. Let’s say that on all the worksheets of the book there is a plate with information for the week. We need to put it all together and bring it to a monthly figure. To get started, you need to watch the following video:

We have four identical plates. The usual way to calculate profit looks like this: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Here, ranges of cells act as arguments.

The 3D sum formula looks like this: =SUM(week1:week4!B2:B8). It says here that the summation is made in the ranges B2:B8, which are located on the worksheets: week (from 1 to 4). There is a step-by-step increase in the number of the worksheet by one.

Sum with multiple conditions

There are times when the user needs to solve a problem that specifies two or more conditions and needs to calculate the sum of numerical values ​​according to different criteria. To implement this procedure, use the function «=SUMMESLIMN».

The step-by-step tutorial looks like this:

1. To begin with, a table is formed.
2. Selects the cell in which the summation result will be displayed.
3. Move to the line for entering formulas.
4. We enter the operator: =SUMMAESLIMN.
5. Step by step, we enter the range of addition, the range of condition1, condition1 and so on.
6. After carrying out all the manipulations, press “Enter”. Ready! The calculation has been made.

It is worth noting! There must be a separator in the form of a semicolon “;” between the arguments of the operator. If this delimiter is not used, then the spreadsheet will generate an error indicating that the function was entered incorrectly.

How to calculate the percentage of the amount

Now let’s talk about how to correctly calculate the percentage of the amount. The easiest method, which will be understood by all users, is to apply the proportion or “square” rule. The essence can be understood from the picture below:

The total amount is displayed in cell F8 and has a value of 3060. In other words, this is one hundred percent income, and we need to find How long of the profit Sasha made. To calculate, we use a special proportion formula, which looks like this: =F10*G8/F8.

Important! First of all, 2 known numerical values ​​u3buXNUMXbare multiplied diagonally, and then divided by the remaining XNUMXrd value.

Using this simple rule, you can easily and simply calculate the percentage of the amount.

Conclusion

The article discussed several ways to get the sum of row data in an Excel spreadsheet. Each method has its own advantages and disadvantages. Using an arithmetic formula is the easiest method to use, but it is more appropriate to use it when working with small amounts of information. For working with large amounts of data, automatic summation is well suited, as well as the SUM function.