Table of Contents:
Excel and Google Sheets: 7 steps to mastery from zero to PRO
Find out moreHow to calculate the sum in Excel using an arithmetic formula
Let's say you need Calculate the sum of two cells in a table—for example, cells A1 and A2. This is a basic operation often used in financial and analytical calculations.
To begin, select the empty cell where you want to see the result. Click the equal sign (=) to indicate that you are about to enter a formula. Then, click the first cell (A1), add the addition operator (+), and select the second cell (A2).
As a result, the following formula will appear in the formula bar and in the selected cell: =A1+A2. Now just press Enter, and Excel will perform the calculation, giving you the total.
This method is most effective for a small number of values - up to three cells. If you need to sum more data, consider alternative methods using the SUM function - they will significantly speed up the process. Excel has many useful functions for working with arrays of data.
How to Effectively Use the AutoSum Button in Excel
The AutoSum button is one of Excel's most convenient tools for quickly summing values in rows and columns. Let's look at how to use this function with a specific example.
Let's say we have a table with car prices at a dealership. To start, select an empty cell in the "Price, rubles" column, then click the "Σ" button on the Excel main tab. In the menu that opens, select the "Sum" option.
If you don't see the "AutoSum" button on the main tab, don't despair! Just go to the "Formulas" section and find it there.
After clicking on "Sum", the sum will appear in the selected cell. The formula for calculating the sum, and Excel will automatically determine the range of cells to be summed. You can edit this range manually if necessary.
In the formula bar, the function will appear as: fx=SUM(E2:E39).
Press Enter, and in The selected cell will display the total amount.
The "AutoSum" button works not only for columns but also for rows. If your catalog prices are arranged horizontally, select an empty cell in the "Prices, rubles" row, then use the "Σ" → "Sum" menu again. Check the suggested range and press Enter. Done!
The "Σ" button also allows you to not only sum up values, but also calculate the average, count the number of cells with data, and set the maximum and The minimum value in a given range.
How to Effectively Use the SUM Function in Excel to Sum Values
The SUM function in Excel is a powerful tool for quickly and accurately summing values from cells. This function allows users to easily aggregate data and perform financial calculations, making it an essential part of working with spreadsheets.
While the AutoSum button in Excel offers the simplest way to use the SUM function, in some cases this method can be inconvenient. For example, when you need to calculate sums from incomplete ranges or combine data from multiple sheets.
Let's look at an example where you need to sum the prices of cars listed on different Excel sheets. First, select an empty cell, and then click the "Fx" button located to the left of the formula bar.
The "Formula Builder" window will appear, where you can find the SUM function. Double-click it to open the argument entry interface.
After that, enter the ranges of values you want to sum. Place the cursor in the "Number1" field and select the first range, for example, E2:E20 with car prices on the first sheet.
Then move the cursor to the "Number2" row and select the range on the second sheet. In the formula builder, this will be displayed in the format ‘Sheet0 (2)’!E2:E20.
To add additional ranges, simply click the "+" button under the "Number2" row in the formula builder.
Click "Done," and the function will perform the calculation, displaying the result in the cell you selected.
In the formula bar, you will see: Fx=SUM(E2:E20; ‘Sheet0 (2)’!E2:E20), which confirms that the entry is correct.
Experienced Excel users often prefer to enter formulas manually. To do this, select an empty cell, enter an equal sign, then the name of the function, and list the ranges, separating them with semicolons.
Optimizing Summation with the SUMIF Function in Excel
The SUMIF function in Excel is a powerful tool for summing data based on a single specified condition. This allows users to extract specific values, for example, summing up car sales of only a certain year or color.
Let's say we have a table of car dealership sales, and we need to sum up data only for manager Trigubov M. Let's look at this process step by step.
To begin, select an empty cell in the table and open the Formula Builder, as you did earlier. Find the SUMIF function from the list of available functions, which will bring up a window for entering the required arguments.
The function requires three main arguments: range, criterion, and summation range.
- The range is the cells in which Excel will search for the specified criterion. In our case, we need to specify the managers column: G2:G39.
- The criterion is the value to search for in the specified range. In our example, this is the manager's last name, which needs to be enclosed in quotation marks: "Trigubov M."
- The summation range is the cells that need to be summed. In this case, these are the cells from the "Price, RUB" column: E2:E39.
After entering the arguments, the formula line will look like this: Fx=SUMIF(G2:G39;"Trigubov M.";E2:E39).
Click the "Finish" button, and Excel will scan the specified ranges, summing only those values that meet the "Trigubov M." criterion. The result will be displayed in the cell you originally selected.
Efficiently Calculate a Sum with the SUMIFS Function in Excel
Unlike the SUMIF function, which allows only one condition, SUMIFS opens the door to more complex and precise analyses.
Let's say you need to sum the cost of cars sold by a certain manager in a specific year—for example, 2021. This is a task that can be easily accomplished using SUMIFS.
To get started, select an empty cell and open the Formula Builder to find the SUMIFS function.
At the initial stage, you will be offered a line for entering the "Sum_range" argument. Here you specify the range of cells whose values you want to sum. In our example, this is the "Price, RUB" column.
Then click the "+" button twice to add rows for entering the first condition: "Range_condition1" and "Condition1".
Fill these lines with the following data for the first condition to calculate the prices of cars sold by manager Trigubov M.:
- Condition 1 range is the "Manager" column: G2:G39.
- Condition 1 — ‘Trigubov M.’.
Now add two more conditions by clicking the "+" button twice to specify The second condition is "Condition_Range2" and "Condition2".
In our case, the second condition concerns the year; we want to sum up the cost of cars produced in 2021. Fill in the arguments as follows:
- The range of condition 2 is the "Year of Release" column: C2:C39.
- Condition 2 is ‘2021’.
After that, click "Done", and the function will search the specified ranges, summing only those values that match both conditions: "Trigubov M." and "2021".
How to use the SUMIFS function in Google Sheets? All the steps above work similarly, except Google Sheets doesn't have a built-in formula builder. Users will have to enter the function syntax manually in the formula bar or in an empty cell, but argument hints will be available when they begin typing the function.
You may also be interested in:
Excel and Google Sheets: From Zero to PRO in 30 Days
Want to automate your work? Learn how to become a PRO in Excel and Google Sheets! Read the article!
Find out more