How to calculate the sum of certain cells in Excel

The MS Excel program allows you to significantly simplify any calculations, including those, for which complex functions are used. And one of the most common and simple actions, performed with the data of the usual addition.

Sometimes even such calculations can cause certain difficulties for novice users of the MS Office package with Word and Excel programs. For example, not everyone even knows that, how to calculate the sum of the selected cells in Excel. Not to mention actions, necessary for adding numbers in the same ranges on different pages. In fact, adding numbers in a table processor is very easy, and only 3 functions and one mathematical sign are used for this.

Simple addition

The easiest way, how to calculate the sum of certain cells in Excel using the plus sign. It is suitable if you need to add a small number of numbers or sum ranges., arranged in random order on one or more sheets.

So, for example, using the plus sign will be advisable if necessary to add up only 3 values, even if they are located very close. A mathematical sign in this case will reduce the time for searching or a set of functions with parentheses. The formula must be preceded by an equal sign.

It is impossible to do without such a method in those cases, when the cells are arranged in an arbitrary order relative to each other. For example, the user knows for sure (by defining it in a way, which cannot be replaced by a formula), what exactly G8 cells need to be folded, H16, J7 and K18. To get the sum, it is enough to simply write an equal sign in the cell first, and then the cell numbers, between which a plus is placed.

If you have to add up a large amount of data, the use of such a technique will take too much time. Even the sum of 4-5 values requires spending time on the placement of advantages and the constant translation of the cursor into a string of formulas. Therefore, it is faster and easier to use the SUM function for this, specially designed for such calculations.

  • Operation

Application of the SUM function

The syntax of the mathematical function is quite simple. After the name of the AMOUNTS appears in the line, enter the required number of arguments. Moreover, there need not be many of them. The function counts the sum even for one number. Although, naturally, using SUMS makes sense only if there are a large number of arguments at least 5.

Function can be applied, adding up individual values or whole ranges. Moreover, the user is not required to put any signs, even semicolons and colons are set automatically. Up to 255 arguments can be specified in one formula. Each of them can be positive, negative, fractional. Rounding occurs according to the cell settings, in which the formula is written.

You can write the word ‘SUM’ manually (in Cyrillic), do not forget to put the opening and closing brackets. Although it is also in the list of functions, which opens when you click on the icon to the left of the formula bar. In most cases, ‘SUMS’ can be found among the 10 recently used functions. If not, you will have to look among the mathematical ones.

  • Operation

Auto sum

Before calculating the sum of several cells in the same range in Excel, for example, in one column or row or several adjacent columns, it is worth getting acquainted with the ‘Auto Sum’ function. It is located in the ‘Editing’ group on the main tab and allows you to automatically determine, which numbers will add up.

The definition is not always correct, but if you select the cell correctly, where is the formula written, the probability will be quite high:

  • if you use auto sum, by selecting the cell under the column with values, all values in this column will be included in the formula,
  • if you place the cursor at the end of a string with numbers and use the function, auto-summation will occur for this range,
  • selecting multiple cells at once, you can get the same amount of auto sums.

If automatic range detection failed, the user can adjust it manually. Anyway, this usually saves time.

Besides, in the drop-down list, which appears when you click the arrow next to the auto sum icon, there are also several other useful functions for example, determination of the minimum or average value. And even a counter, which shows the exact number of values being added.

Features of determining the amount in different situations

One of the simplest calculations, for which the SUM function is used - the addition of numbers in a column. To determine the amount, it is enough to select the upper cell when specifying the argument, press the right mouse button and release, by selecting the desired range.
If the result of addition should be in the first line after the summed values, you can not specify the function at all, and just click on the auto sum sign. The value will be set to the desired location.

In the same way, you can sum up the number of cells with a certain value for a row. The function is selected, the desired range is highlighted when the argument is specified. And if the result needs to be put in the next column of the same row, you can save time by using an auto sum.
 
It may be necessary to determine the result of addition for the same range, located on several pages at once. For example, to determine the amount of expenses for the same items of each month. In this case, the formula =SUMS(January:April!B4) will output a number, obtained by adding cell B4 on each of the 4 specified sheets.

Another interesting addition function allows you to take into account only those cells of the table when calculating, which are displayed on the screen. Values from the hidden range are not taken into account. It is called ‘Interim results’ and is intended for more complex calculations, than simple addition. But you can also use it to get the amount.

For example, INTERMEDIATE.RESULTS(109, G5:G19) will show the sum in the range from G5 to G19 excluding cells, which the user has hidden manually (for example, 11 and 12). If the rows are hidden during filtering, instead of the argument 109, you need to enter the number 9.

Read also:

  • The best budget tablets: which one to buy in 2021
  • How to monitor a child’s location via iPhone