How to Use Excel Dsum
Microsoft Excel provides many built-in functions for analyzing and creating data on a spreadsheet. Many of these are popular, basic and relatively self-explanatory, such as the "Sum" and "Average" functions, which simply add together data, or calculate an average among a range of data, respectively. The "DSum" function is a more complex variation of "Sum" where the values may be filtered directly by the function itself before added together. Additionally, the "DSum" function can look across a broad range of cells when making is calculation, and work with the relationship between separate columns. In this way, a "DSum" formula can be created just once, and criteria fields adjusted elsewhere in a spreadsheet to manipulate the outcome of the results.
Other People Are Reading
Instructions
-
-
1
Open Microsoft Excel.
-
2
Create a new worksheet or load an existing spreadsheet. The data must be organized in a columnar fashion, such that many rows share the same type of information in each column. The top row of the spreadsheet must contain the header cells that define the type of data each column contains. These are simple text labels and any word or words may be chosen.
-
-
3
Insert three new rows at the top of the spreadsheet. Right-click on the top row, row 1, on the left side of the spreadsheet and choose the "Insert" option from the pop-up menu. A new row will be inserted. Repeat this two more times until three blank rows are above the rest of the sheet's data.
-
4
Type, or copy and paste, the same exact header cells from your data into the new blank row 1. A quick way to complete this is to right-click on the row 4, where your data now starts, and choose "Copy" from the pop-up menu. Then right-click in cell A1 and choose "Paste."
-
5
Type into row 2, under the new header cells, the criteria that will filter your spreadsheet data before summing up the cell ranges you will select in the "DSum" function. You may have hundreds of rows of individual order details, for example, but only wish that orders which exceeded $5 be included in the sum. If a column existed for "Total Cost," you could place the notation ">5" in the cell under the "Total Cost" header to create this criterion. Any combination of criteria for one or more columns may be entered, and only rows that meet all criteria will be included in the summation.
-
6
Click any other cell in the spreadsheet where you wish to create the "DSum" formula. Type an equal sign ("=") to begin the construction of the formula. Follow the equal sign with the word "dsum" (case insensitive) and an open parenthesis.
-
7
Type the range of cells that denote the full set of data in your spreadsheet. For example, if you have 10 rows of data that span five columns, the data begins on row 4 and extends to row 13, and starts in column A and stretches to column E. The range notation is therefore "A4:E13." Type a comma after this range.
-
8
Type the name of the header cell that reflects the column you are adding up. Enclose this header text in quotes and type it exactly as it appears in the header cell at the top of the data. If you wished to sum up the total cost of all orders that meet your criteria, you could type "Total Cost" if that was the name of the column that contained this information. Type a comma after this text.
-
9
Type the range that denotes where your filter criteria is located on the spreadsheet. In this example, the criteria has been typed into row 2. If the spreadsheet spans five columns, this range notation is "A1:E2" to reflect the section of the spreadsheet at the top that contains the criteria. Type a closing parenthesis and press "Enter." The "DSum" formula is constructed and a result is displayed that sums together the data in the specified column from all the rows that meet your criteria.
-
10
Change the criteria in row 2 to have the "DSum" formula automatically select different rows for the sum calculation.
-
1
