PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
EXCEL provides a complete set of functions to deal with the database data.
DATABASE data has the first row as headers.
DSUM FUNCTION ADDS THE VALUES IN A COLUMN [ FIELD ] WHICH FULFILLS A GIVEN CONDITION.
DSUM function is very useful if we need to find out the sum of data on the basis of any classification which can be changed by changing the condition.
DSUM FUNCTION ADDS THE NUMBERS PRESENT IN A COLUMN [ FIELD ] WHICH SATISFIES THE GIVEN CONDITION.
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
The Syntax for the DSUM function is
=DSUM(database or range, column or field, Criteria )
The DSUM function syntax has the following arguments:
Database Database is the data on which we want to use the DSUM function. The data is specifically arranged in DATABASE STYLE with LABELS in the top row and data in the lower rows. The range mentioning the database is given including the cells containing the Labels.
Field Column or field is the column on which the criteria will be applied. The column is given by the column name in the “” or it can be given as the column number of the database such as 1 ,2 and so on.
Criteria It is the range which contains the criteria or conditions. It is important that the range contains the column label. It’ll become clearer with the example discussed below.
TEAM | SALES | EARNING |
A | 148 | 867 |
B | 234 | 671 |
C | 102 | 936 |
D | 104 | 765 |
E | 216 | 696 |
F | 219 | 599 |
G | 184 | 568 |
A | 231 | 822 |
B | 235 | 663 |
C | 238 | 915 |
D | 114 | 598 |
E | 211 | 563 |
F | 240 | 831 |
G | 139 | 926 |
The function used for the solution is =DSUM(D5:F19,”SALES”,H5:J6)
Let us analyze the formula used.
The first parameter i.e. D5:F19 is the complete range of the Database [data ].
The second parameter “SALES” is the field or column on which the condition would work. As we want to know the total sales, SALES column is needed.
The column is always given in the Double Quotes. [” “].
The last parameter is the range containing the Criteria H5:J6.
Have a look at the way criteria is described. The column names must be same as the database so that the function can lookup and give you the result.
The condition is given below the column.
The function used for the solution is =DSUM(D5:F19,”EARNING”,H12:J13)
Let us analyze the formula used.
The first parameter i.e. D5:F19 is the complete range of the Database [data ].
The second parameter “EARNING” is the field or column on which the condition would work. As we want to know the total Earnings, EARNING column is needed.
The column is always given in the Double Quotes. [” “].
The last parameter is the range containing the Criteria H12:J13.
Have a look at the way criteria is described. The column names must be same as the database so that the function can lookup and give you the result.
The condition is given below the column.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE