Excel CountIFs Function

Excel CountIfs is a built-in statistical function. It can also be used as a worksheet function in addition to other formulas in a cell of a worksheet.

Syntax

=countIfs( criteria_range_1, criteria_1, criteria_range_2, criteria_2, …..criteria_range_n, criteria_n)

Arguments

• Criteria_range_n. Range of cells that you want to apply against criteria_n
• Criteria_n. The criteria used to determine which cells to count.

Where n=1,2,3,4,5….

Purpose

To count a number of cells in a range that meets a single or multiple criteria

Return Type

Numeric

Remarks

• Each additional range must have the same number of rows and columns as in the first range
• Both contiguous and non contiguous ranges are allowed
• If the criteria is a reference to an empty cell, the countifs function treats it as zero

Excel CountBlank Function

Excel Count Blank is a built-in statistical function. It can be used as a worksheet function means as a part of formula in a cell of a worksheet. The function counts only the number of blank cells; cells containing text, numbers, errors, etc. are not counted.

Syntax

Countblank(range)

Arguments

Range: REQUIRED. Range of cells to count empty cells

Purpose

To count the empty cells in a range

Return Type

Numeric

Remarks

• Formulas that return empty text (“ ”)are considered blank and will be counted
•  Cells containing zero are considered non blank and thus will not be counted

Excel Count Function

A built-in excel function, Count Function counts the number of cells containing numbers as well as the number of arguments that contain numbers. It is categorized as a statistical function that can be used as a worksheet function.

Syntax

=count (value1, value2,…)

Arguments

• Value1: An Argument/ cell reference/ range
• Value2. Optional. An Argument/ cell reference/ range

Purpose

To count the number of cells containing numbers

Return Type

Numeric

Remarks

• It can handle upto 255 additional values
• For counting logical and text values, use CountA Function and CountIf to count on certain criteria
• Logical values are not counted, but with other statistical functions like ISNUM, MBER, ISTEXT, ISLOGICAL, ISFORMULA, etc., it is possible.
• Empty cells, errors and text are not counted
• Date and time are stored as numbers in Excel, so excel Count Function counts both

Excel CountIF Function

Excel CountIf Function is a built-in excel statistical function. It is a worksheet function that can be used as a part of formula in a cell of a worksheet.

Syntax

=countif(range, criteria)

Arguments

• Range. Required. The range of cells that you want to count based on certain criteria.
• Criteria. Required. Criteria to determine which cells to count

Purpose

To count the number of cells based on a single criterion

Return Type

Numeric

Remarks

• CountIf function supports logical operators - >,<,<>,= and wildcards *,? For partial matching.
• Criteria can also be based on a value from another cell using concatenation.
• CountIf is not case-sensitive.
• In criteria, do not forget to use double quotes for text, while numbers can be passed without quotes.
• When logical operator is used with a number, enclosed them with quotes.
• Value from another cell can also be used in criteria using concatenation ex”</>/=”&cell
• Wildcards * and ? can be used in criteria

Excel CountA Funtion

CountA - Count Anything except blanks

CountA is a built-in excel function that is categorized as a statistical function. It can also be used as a worksheet function. CountA function is used to count cells containing numbers, texts, logical values, error values, and empty text returned in formulas (“ ”).

Syntax

=CountA (value1, value2,….)

Arguments

• Value1: An Argument / Cell Reference/ Range
• Value2: Optional. An Argument/ Cell Reference/ Range

*Argument is a parameter that is not a cell or a range of cells.

Purpose

To count the number of non-blank cells.

Return Type

Numeric.

Remarks

• It does not count cells that are completely empty.
• If you forgot to mention the arguments in the range, with CountA Function, the arguments can be passed separately in the formula. Ex. =COUNTA(c1:c5, a,b). It will return the sum of non-empty cells between c1 & c5 and 2.
• The major difference between Count and CountA functions is, Count Function counts cells containing numbers but CountA counts all cells that are non-empty. If need arises to count empty cells, use CountBlank function that will count only empty cells.

Concept of Invisible Characters:

Keep in mind that CountA will count the empty cell that actually contains invisible characters (accidentally type a space in a cell) or an empty string returned by (“”) formula. To check this

• Select the range
• Press Ctrl+G 
• Click on SPECIAL one the left bottom
• Select BLANK and ok to check such cells

Excel EDATE Function

EDATE is a built-in excel Date/ Time function and it is used to add or subtract a specified number of months to a date and fetches the result as a serial date. It is a worksheet function means it can be used in combination with other functions. Excel Edate function is good to use to calculate the maturity dates or due dates that fall on the same day of the month as the date of issue.

Purpose

To shift date n months in future or past

Syntax

EDATE(start_date, months)

Arguments

• Start_date. REQUIRED. The start day from which future or past number of months will be counted.

Dates should be entered by using the DATE function or as results of other formulas. The function will return errors if dates are entered as text

• Months. REQUIRED.A number of months to add or subtract

Return Type

New date in future or past as excel serial number

Return Value

Date in serial number form

Remark.

• The Excel EDATE function returns a serial number representing the date. To force Excel to display it as a date, you should apply the Date format to the cells with your EDATE formulas
• If a decimal value is provided for months, the EDATE function will only add the integer portion to start_date
• If start_date is not a valid date, the EDATE function will return the #VALUE! error

Excel ROUNDDOWN Function

A built-in Math/ Trig function, ROUNDOWN function can be used as a worksheet function.

Purpose

To round a number down, toward zero

Syntax

=ROUNDDOWN(number, num_digits)

Arguments

• Number.REQUIRED.Any real number that you want to round down.
• Num_digits.REQUIRED. The number of digits to which you want to round down the number.

If Num_Digits is:

• Zero (0). The number is rounded down to the nearest integer.
• Greater Than Zero (>0). The number is rounded down to the specified number of places in the right of decimal point.
• Less than zero (<0). The number is rounded down to the specified number of places in left of the decimal point.

Return Type

Number

Remarks

ROUNDDOWN behaves like ROUND. The only different is ROUNDDOWN always rounds a number down.