COUNTIF dan SUMIF

Sudah lama aku mencari function excel ini, baru kemarin ketemu.

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, or text that defines which cells will be counted. For example, criteria can be expressed as 32, “32”, “>32”, “apples”.

Remark
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, 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.

Example

Formula Description (Result)
=COUNTIF(A2:A5,”apples”) Number of cells with apples in the first column above (2)
=COUNTIF(B2:B5,”>55″) Number of cells with a value greater than 55 in the second column above (2)

SUMIF
Adds the cells specified by a given criteria.

Syntax
SUMIF(range,criteria,sum_range)

Range
is the range of cells you want evaluated.
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”, “apples”.
Sum_range
are the actual cells to sum.

Remarks
The cells in sum_range are summed only if their corresponding cells in range match the criteria.
If sum_range is omitted, the cells in range are summed.
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, 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.

Example

Formula Description (Result)
=SUMIF(A2:A5,”>160000″,B2:B5) Sum of the commissions for property values over 160000 (63,000)