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.

Excel Round Function

Round function is a built in excel function categorized as Math/ Trig Function. It can be used as a worksheet function in excel and VBA.

Purpose

To return a number rounded to the specified number of places

Syntax

=ROUND(Number, Num_Digits)

Arguments

• Number.REQUIRED. The number that you want to round
• Num_Digits. REQUIRED. The number of digits to which you want to round the number argument

If Num_Digits is:

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

Return Type

Number

Remarks

• To always round up (away from zero), use the ROUNDUP function
• To always round down (toward zero), use the ROUNDDOWN function
• If a number is already rounded to the given number of places, no rounding occurs

Excel INT Function

Excel INT Function

An excel function that returns the integer part of the decimal number by rounding down to the integer. It can be used as a worksheet function or a VBA function. The function is categorized as math and trig function in excel.

For example: INT(10.8) will return 10 and INT(-10.8) will return -11.

So, we can say, negative numbers become more negative as INT function rounds down.

Syntax

=INT(Number)

Argument

Number. REQUIRED. Any positive or negative decimal digit from which integer is required

Purpose

Get the integer part of a decimal number by rounding down

INT function returns the integer part of the decimal digit by rounding down. However, if you only want the exact the integer part of both negative and positive numbers, it is good to use TRUNC Function.

Remarks

Excel provides a number of functions for rounding:

• To round normally, use the ROUND function.
• To round to the nearest multiple, use the MROUND function.
• To round down to the nearest specified place, use the ROUNDDOWN function.
• To round down to the nearest specified multiple, use the FLOOR function.
• To round up to the nearest specified place, use the ROUNDUP function.
• To round up to the nearest specified multiple, use the CEILING function.
• To round down and return an integer only, use the INT function.
• To truncate decimal places, use the TRUNC function.

Source:

https://exceljet.net/excel-functions/excel-int-function

Excel TRUNCFunction

Excel TRUNC Function

Introduced in Excel 2007, TRUNC function is a built-in excel function that can be used as a worksheet function. It is categorized as Math and Trigonometry Function that does not round off the number. It is advised to use TRUNC function to obtain the integer value of the given decimal number.

For Example:=TRUNC(10.2) or =TRUNC(10.2,0) will return 10, =TRUNC(10.2,1) will return 10.2

Syntax

=TRUNC(Number, Num_Digits)

Arguments

• Number. REQUIRED. Any positive or negative number that is required to truncate.
• Num_Digit. Optional. Could be a positive, negative digit, or zero. If this parameter is omitted, trunk function will assume 0.

-A positive value that is greater than zero specifies the number of digits to the right of the decimal point.

-equal to zero (or left blank) specifies to truncate to the nearest integer.

- A negative value less than zero specifies digits to the left of the decimal point.

Purpose

To truncate a number to a given precision

Return Value

A truncated number

Remarks

• The function does not rounds off the number, just returns the output integer/ and non-integer depending upon the input.
• The difference between INT and TRUNC is that INT returns on integer value by rounding off to the nearest integer but Trunc does not rounds off the number and return the integer and non-integer output depending upon the input.
• TRUNC and INT are similar functions because they both return the integer part of a number. The difference is that TRUNC merely truncates a number, but INT rounds off a number. However, for positive numbers and when trunk uses 0 as the num_digit, both functions return the same value. With negative numbers, results will be different.

Source

• https://corporatefinanceinstitute.com/resources/excel/functions/trunc-function/
• https://support.office.com/en-us/article/trunc-function-8b86a64c-3127-43db-ba14-aa5ceb292721
• https://exceljet.net/excel-functions/excel-trunc-function