How to Use Excel's DSUM Function



How to Use Excel's DSUM Function

By an eHow Contributor

I want to do this! What's This?

Excel's DSUM function is short for "database sum." It adds the values in a column of a database that match the specified criteria. DSUM then calculates its return value in the same manner as SUM, which simply takes its values directly from the argument list. The following steps will show how to use Excel's DSUM function.

[pic]Difficulty: Moderate

Instructions

1.

Learn the syntax of DSUM (database, field, criteria). Database is a cell range that specifies the database, field is a column in the database from which DSUM will add values and criteria is a cell range with the criteria to be used in selecting the values.

2.

Define the database for DSUM. The first cell in the range will indicate the upper left corner of the database array and the second cell will indicate the lower right corner of the array. The first row of the array must contain the column names.

3.

Specify the field for DSUM. It can include the column name enclosed in double quotation marks or a number representing the column's position in the database where 1 is the first column, 2 is the second column and so on.

4.

Supply the criteria for DSUM. This cell range must contain at least one column name and one condition for that column. The condition will generally be of the form ="condition." Thus, a value of ="=Apple" would select rows where the value in the specified column is equal to "Apple."

5.

Enter the following in the first four columns of an Excel spreadsheet:

Row 1: Tree, Height, Age and Yield;

Row 2: ="=Apple" and =">10" (nothing in columns three and four);

Row 3: ="=Pear" (nothing in columns two, three and four);

Row 4: Tree, Height, Age and Yield;

Row 5: Apple, 18, 20 and 14;

Row 6: Pear, 12, 12 and 10;

Row 7: Cherry, 13, 14 and 9;

Row 8: Apple, 14, 15 and 10;

Row 9: Pear, 9, 8 and 8; and

Row 10: Apple, 8, 9 and 6.

6.

Interpret the formula =DSUM(A4:D10,"Yield",A1:B2) as follows: The database is defined as the array A4:D10. Note the column names in the first row of this array. "Yield" is the column name from which the values will be taken. The criteria are given by A1:B2 which indicates that the column named Tree must contain "Apple" and the column named Height must contain a value greater than 10. DSUM(A4:D10,"Yield",A1:B2) will therefore be evaluated as SUM(14,10)=24.

If Function

One of the most useful functions in Excel is the IF function. What it does is test a value in a specific cell to see if that value meets a certain condition. If the value does meet the condition, one result will happen, if false, a different action will occur.

Writing the IF function:

=IF(logical_test, value_if_true, value_if_false)

IF Function example:

=IF(D1 < 26, 100, 200)

Breaking the example down into its three parts we have:

logical test: D1 < 26, (" ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download