DSUM Function
Adds numbers based on criteria stored in a separate range. The criteria can be as complex as required.
DSUM(database,field,criteria)
Where database is a range of cells in data format, field indicates which column in database is to be used in the calculation and criteria is a reference to a range of cells where the criteria values are entered.
The field argument can be specified either by using the column label as a literal text value, such as "Units" or "Country", enclosed in double quotes and with the exact same spelling as the column label in the first row of database (watch out for rogue space characters) or they can be specified as column index values, i.e. the first column in the list is 1 etc.
The criteria argument must be a reference to a separate range of cells, you can not contain the criteria values inside the formula. The range must consist of at least two cells and be arranged in data format. The criteria range can be expanded to encompass any set of criteria values and it can be external to the worksheet where the formula is entered.
When you add additional criteria values to the range on the worksheet you must also remember to expand the definition of the criteria range in the formula, similarly you must reduce the range if you remove criteria. To contain the criteria within just two cells and still produce complex criteria, use a calculated criteria.
Calculated criteria in database formulas
Calculated criteria using a two cell criteria range is a much neater implementation than the ever-changing multiple cell criteria range for database formulas. But you must comply with some strict rules to get them to work properly:
You can perform similar types of aggregation calculations using Excel Pivot tables, they are much easier to do but they are not formulas and do not recalculate automatically as database formulas do, see Pivot Tables Level 3 manual.
Other database functions such as DCOUNT, DCOUNTA, DMAX, DMIN, DPRODUCT,
DAVERAGE etc. take the same form.
When you have many database formulas to enter it can take some time to set up all the corresponding criteria ranges for the formulas to refer to. For a macro to automate this process see Setting up criteria for Database formulas.
Excel Criteria Expressions
Excel criteria expressions are entered as arrangements of values in separate worksheet cells which are used to specify the criteria values for Database functions and Advanced Filters. You refer to the cells containing the criteria expression in the formula or filter.
Calculated criteria
You can use a calculated value that is the result of a formula. When you use a formula to create a criterion, do not use a column label for the criteria label; either keep the criteria label blank or use a label that is not a column label in the range. It is a convention to just type the word "Calc" into the label cell.The formula you use for a condition must use a relative reference to refer to the cell that is the first data cell under the header row. All other references in the formula must be absolute and the formula must evaluate to TRUE or FALSE.
The advantage of the calculated criteria is that the criteria definition range can be reduced to two cells and yet quite sophisticated criteria can be expressed.
Examples:
Where the Product is "Soap: =B2="Soap"”
Where the Product is not "Soap":
=NOT(B2="Soap") or =B2<>"Soap"
Where the country is "UK", "France" or "Germany":
=OR(A2="UK",A2="France",A2="Germany")
Where the Product is "Soap" or "Shave" and the Value is greater than or equal to 2000:
=AND(OR(B2="Soap",B2="Shave"),D2>=2000)
Where the Units figure is above the average of the Units:
=C2>AVERAGE($C$2:$C$6)
Where the Value figure is less than the median value:
=D2<MEDIAN($D$2:$D$6)
Excel Wildcard Characters
The following wildcard characters can be used as comparison criteria for filters, and when searching and replacing content:
For Further Course Details Visit Our Homepage
Microsoft Accredited IT Training Provider
Microsoft Certified Training Materials
IT Training Courses delivered by Microsoft Accredited Facilitators
Microsoft MOS Test Centre London
No comments:
Post a Comment