Excel spreadsheets are designed to store data and perform calculations in them, as well as to analyze information and collect statistics. This means that the user needs methods of calculating the totals and statistics that satisfy his requirements. For this, the program provides various features.
Using Functions
One of the most popular tasks for Excel users is to calculate the amount of data stored in one column. We’ll learn how to summarize a column in Excel using various methods. The easiest way to find the sum of the data located on a sheet is to apply the formula. And the simplest of them is the SUM () function. Let us show her work as an example.
Travel agency keeps track of tourists who arrived at the hotel during the reporting period. The hotel provides the company with rooms that the company offers its guests for accommodation. Guests pay for the rest of the company, and the company itself pays for rooms in the hotel. The task is to calculate how much money the company must pay to the hotel, how much tourists need the agency, how many people will arrive during the indicated period, and how many guests will spend in the hotel for the total number of nights.
To summarize a column in Excel as a set of cells, we use the formula. To insert it, add an equal sign in the resulting cell and select the SUM function (). In the opened window for entering arguments, specify the column from which data will be added. In the final field we get the sum of the column values.
AutoSum
Another way to get totals is to use subtotals. In our case, we are talking about auto data sum. This method only works if the range of cells is formatted as a table. If this is an unformatted data set, the function does not work.
To summarize a column in Excel as a subtotal, use the AutoSum command on the Edit toolbar of the Home tab. The menu of the indicated button contains the selection items and other totals: calculating the average, minimum and maximum values and other functions.
To insert a car amount, select the desired cell. For example, we sum the numbers in a column in Excel “Number of Nights”. Set the cursor on field H10 and press the “AutoSum” button. The result instantly appears in the desired cell. When inserting rows into a table or deleting them, the range of totals automatically expands or narrows, maintaining the relevance of the calculation.
Summation by condition
Sometimes the task is to add to the total not all the values in the column of the table, but only those that meet the specified requirements. For such a case, the SUMMES function () is provided . Before summarizing the columns of the Excel table, we determine the conditions for adding cells to the sum and indicate them in the function arguments. Command syntax:
SUMMS (range; condition; [sum_range]).
By the range we define the column in which we need to summarize the data. Then we write down the requirements for this data. We calculate in the table what is the total amount at the request of tourists, each of which exceeded 100 thousand rubles.
To do this, call the SUMMS function and specify the attributes:
There is a command in the program that allows you to specify several conditions for adding a number to the sum. This is SUMMER. Its syntax is:
SUMMER (sum_range; condition_range1; condition1; [condition_range2; condition2]; ...).
Let's see how to summarize columns in an Excel spreadsheet with several conditions. For example, we calculate the final price of applications for tourists who ordered breakfast and staying in hotels for more than two nights.
The function line looks like this:
= SUMMES ((Full price]; [Food]; "Breakfast"; [Number of nights]; "> 2").
Summary tables
For even more complex totals, you can create pivot tables. They allow you to calculate totals by category. In our case, we add a summary table that shows the total number of tourists, nights of accommodation, amount to the hotel and from guests by room category.
To create a pivot table, select the “Pivot table” command of the “Tables” toolbar of the “Insert” tab. In the creation wizard, we set the table or range from where the data will be taken. The ability to use a range rather than an entire table allows Excel to summarize a column starting from a specific row, rather than all column values.
Next, we determine where the report will be placed, select the fields to display and the type of totals, and get the amounts for the desired columns.
In this article, we looked at ways to summarize a column in Excel. Each of them has its own advantages and in different cases may be more convenient than others. It remains to choose the one that suits your tasks and requirements and gives you the desired result with minimal effort.