How to Use DSum in a Datasheet

DSum adds the numbers in a column of a list or database that match conditions that you specify.

DSum is a function in a Microsoft Excel spreadsheet. It adds the numbers listed in rows and columns based on a given criteria. The syntax for the DSum function is: DSum( range, field, criteria).

Let's say a charity organization wants to analyze the fund-raising capacity of its patrons based on gender and age. This can be done using the DSum function in Excel.

Instructions

1. Determine the range you want to include in the Dsum function. The range typically includes a list of calculable numeric characters arranged in rows and columns. Column headings are not part of the range. For example, let's say there are 50 men and 50 women. Each patron will have a dollar amount assigned to their name for funds they have collected. Therefore, all 100 different dollar amounts in the spreadsheet will be the maximum range of funds that can be used for DSum. Also, let's assign the value that male is equal to 1 and female is equal to 2. The range, in terms of gender, will be 1 and 2.

2. Determine the field you want include in the DSum function. A field indicates the column heading that is used in the DSum function. The column label is entered in the function enclosed between double quotation marks. In our example, the three possible fields will be "Gender", "Age" and "Funds."

3. Determine the criteria you want apply in the DSum function. A criteria cell in the spreadsheet contains a conditional statement such as ">", "<" and "=" For example, if the charity wants to know the total number of patrons who raised more than $1,000 in charity fund. The criteria will be ">1000" and that's how it will be entered in the criteria cell without the double quotation marks. If the charity wants to know the total number of funds collected by female patrons. The criteria will be "=2". Criteria is a conditional statement either range or field have to satisfy to be included for computation.

4. Calculate DSum. From our example, let's say the charity wants to know the total amount of funds collected by women and the total amount of funds collected by patrons younger than 30 years.

   The column heading "Funds" appears in cell A1, "Gender" appears in B1 and heading "Age" appears in C1.

   Let's enter the gender criteria "=2" in cell B2 and age criteria "<30" in C2 without quotation marks

   Let's sort the rows -- excluding column headings -- in the descending order based on "Gender".

   Now all cases assigned with the value of female = 2, will appear in rows B3 to B52 in the Gender column. All cases assigned with the value male = 1 will appear from B53 to B102.

   All funds collected by female patrons will appear in rows A3 to A52 in the Funds column. All funds collected by male patrons will appears in rows A53 to A102.

   Age of patrons will appear in rows C2 to C103.

   The DSum function to know the total amount of funds collected by women will be: DSum(A2:A101, "Gender", B1:B2.).

   The DSum function to know the total amount of funds collected by patrons younger than 30 will be: DSum(A2:A101, "Age", C1:C2).

References

• Microsoft Office: DSum
• Tech on the Net: MS Excel: DSum Function