Sunday, July 19, 2009

MS Excel 2003 Training 101 - SUMIF and COUNTIF

SUMIF

Adds the cells specified by a given criteria.
Syntax
SUMIF(range,criteria,sum_range)


Range   is the range of cells that you want evaluated by criteria.
Criteria   is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".
Sum_range   are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.
Remarks
Sum_range does not have to be the same size and shape as range. The actual cells that are added are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:



You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
Microsoft Office Excel provides additional functions that you can use to analyze your data based on a condition or criteria:

To count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function.
To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function.
To analyze data in a list based on criteria, such as profit margins or product types, use the database and list management functions (DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP).



COUNTIF

Counts the number of cells within a range that meet the given criteria.
Syntax
COUNTIF(range,criteria)


Range  is the range of cells from which you want to count cells.
Criteria  is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
Remarks
You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition.
To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function.
To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.

Example 1: Common COUNTIF formulas



Example 2: COUNTIF formulas using wildcard characters and handling blank values



Note To view the number as a percentage, select the cell and click Cells on the Format menu. Click the Number tab, and then click Percentage in the Category box.

No comments:

Post a Comment