Commonly Used Excel Functions in Accounting You Need to Know

In addition to professional accounting software such as Misa, Fast, Mcom…, Excel is widely used in the work of accountants. Mastering commonly used Excel functions often helps accountants save time and effort compared to manual calculation methods. Today, Software Tips and Tricks will list and guide you through the usage of some commonly used functions in accounting.

Addition Subtraction Multiplication Division Functions

Addition Operation

Syntax: =SUM(number1,[number2],…)

Where:

• SUM: is the function used to calculate the sum.
• Number1: is the first argument.
• Number2,…: are the second and subsequent arguments (optional).

Arguments can be a range of data, a number, a single cell reference, and are separated by commas (,).

Alternatively, you can use the plus sign (+) to perform addition.

Subtraction Operation

Syntax: =number1 - number2

Where:

• Subtraction Operator (-):  is the mathematical operator used to perform subtraction.
• Number1: is the number being subtracted.
• Number2: is the number to subtract.

Number can be a number or the location of a cell containing data.

Example: You type:  = 5-2 into a cell => the result is 3.

Multiplication Operation

Syntax: =PRODUCT(number1,[number2],…)

Where:

• PRODUCT: is the function used to multiply factors..
• Number1: is the first factor.
• Number2,…: are the second and subsequent factors (optional).

Number can be a range of data, a number, a single cell reference, and are separated by commas (,).

Alternatively, you can use the asterisk (*) to perform multiplication.

Example: You type the formula: =PRODUCT(2,6,9) into a cell => the result is 108.

Division Operation

Syntax: =number1 / number2

In this case:

• Slash (/): is the arithmetic operator used to perform division.
• Number1: is the dividend.
• Number2: is the divisor.

Number can be either a number or the cell reference containing the data.

Example: You input in a cell: =10/3 => the result obtained is 3.33333…

AVERAGE Function

Syntax: = AVERAGE (number1, [number2], …)

Where:

• AVERAGE: is the function used to calculate the average value of the arguments.
• Number1: is the first argument.
• Number2,…: are the second and subsequent arguments (optional).

Arguments can be a range of data, a number, a single cell reference, and are separated by commas (,).

Example: =AVERAGE(1,2,3) => returns the result as 2.

SUBTOTAL Function

Syntax: = SUBTOTAL (function_num,ref1,[ref2],...)

Where:

• SUBTOTAL: is the function name used to calculate visible rows. Rows hidden by Filter are ignored in the calculation.
• Function_num: ranges from 1-11 or 101-111 specifying the function used for subtotals. 1-11 includes manually hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
• Ref1: is the range or reference first named range for which you want to calculate subtotals.
• Ref2,...: optional values. Range or array of named ranges from 2 to 254 for which you want to calculate subtotals.

For example, if you want to sum the total of filtered rows using the Filter tool, in the total cell, you use the formula: =SUBTOTAL(9,G3:G12). Here, 9 represents the sum request, G3:G12 is the data range to which the formula applies. When using the filtering tool as per requirement, the total cell will only sum the filtered rows.

Total revenue filtered by Hanoi Branch:

Total revenue filtered by Q1/2018:

Search function (VLOOKUP and HLOOKUP)

VLOOKUP

Function syntax: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

In this:

• VLOOKUP: is the function used to search for data vertically (up and down).
• lookup_value: is the value to be searched for.
• table_array: is the reference table.
• col_index_num: is the column number in the reference table containing the value to be returned.
• [range_lookup]: is an optional argument. Returns an approximate or exact match – declared as either 1/TRUE (approximate), or 0/FALSE (exact).

For example, if you have a price list to fill in, you can use the VLOOKUP function with the following formula:

Note:

• The first column must contain the data to be referenced.
• The reference range must always be absolute. (Unless you have other requirements).
• If the HLOOKUP function returns an error value #N/A it means the value to be found is not in the reference table. You should check the reference table and add the information if necessary.

HLOOKUP Function

Function syntax: = HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

In which:

• HLOOKUP: is a function used to search for data horizontally.
• lookup_value: is the value to be searched for.
• table_array: is the reference table.
• row_index_num: is the column number in the reference table containing the value to be returned.
• [range_lookup]: an optional argument. Returns an approximate or exact match - specified by declaring two values as 1/TRUE (approximate) or 0/FALSE (exact).

Using the HLOOKUP function is similar to VLOOKUP, except HLOOKUP searches for data by row.

IF Function

Function structure: = IF (Logical_test, [value_if_true], [value_if_false])

In the following cases:

• IF: is the name of the conditional function that returns a value of 1 if the condition is true, and a value of 2 if the condition is false.
• Logical_test: is the reference condition.
• [value_if_true]: is an optional argument. It is the value returned if the Logical_test condition is true. If the user does not input a value_if_true value, Excel will return TRUE if the condition is true.
• [value_if_false]: is an optional argument. It is the value returned if the Logical_test condition is false. If the user does not input a value_if_false value, Excel will return FALSE if the condition is false.

For example, you have the following table of scores that need to be classified:

• For candidates with a score greater than or equal to 5 => Pass.
• For candidates with a score below 5 => Fail.
  We use the formula for cell C2 as follows: =IF(B2>=5,'Pass','Fail').

The Functions of SUMIF and SUMFIS

Structure of the SUMIF function: =SUMIF (range, criteria,sum_range)

In which:

• SUMIF: is the function used to calculate the sum based on a specified condition.
• Range: is the reference range containing the condition data.
• Criteria: is the condition that needs to be met.
• sum_range: is the range for sum calculation.

Example: You have a revenue table as shown below, and you need to calculate the total revenue of the Hanoi branch. Enter the formula into a cell in Excel worksheet =SUMIF(B3:B12,'Hanoi Branch',G3:G12).

Structure of the SUMIFS function: = SUMIFS sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

In which:

• SUMIFS: is the function used to calculate the sum that meets multiple conditions.
• sum_range: is the range for sum calculation.
• criteria_range1,2,3…: is the reference range containing condition data 1,2,3...
• Criteria1,2,3…: are the conditions to be met 1,2,3...

With the revenue example above, we need to calculate the revenue of the Hanoi branch in the first quarter of 2018, we use the formula: =SUMIFS(G3:G12,B3:B12,'Hanoi Branch',C3:C12,'1/2018').

The Functions AND and OR

Function structure: = AND(Logical1; [Logical2]; [Logical3];…)

In which:

• AND: is the function name that means AND, and returns TRUE when all arguments are true.
• Logical1,2,3…: Arguments are constants, logical expressions.

Function structure:= OR(Logical1; [Logical2]; [Logical3];…)

In which:

• OR: is the function name that means OR, and returns FALSE when all arguments are false.
• Logical1,2,3…: Arguments are constants, logical expressions.

Example to differentiate AND and OR:

AND(TRUE,FALSE) = FALSE

OR(TRUE,FALSE) =TRUE.

The Functions MIN, MAX

Function structure: =MIN(number 1, number 2, …)

Where:

• MIN: is the function name used to return the smallest value in a series of numbers.
• number 1,2…: are the series of numbers to find the smallest value.

Function structure: =MAX(number 1, number 2, …)

Where:

• MAX: is the function name used to return the largest value in a series of numbers.
• number 1,2…: are the series of numbers to find the largest value.

Example: MIN(10,5,16) =5 and MAX(10,5,16)=16.

The Functions LEFT, RIGHT, MID

The LEFT, RIGHT, and MID functions allow users to extract a certain number of characters from a string.

Syntax:

= LEFT(text, [num_chars]).
= RIGHT(text, [num_chars]).
= MID(text, start_num, num_chars).

Where:

• LEFT: is the function name to extract characters from left to right of the text string.
• RIGHT: is the function name to extract characters from right to left of the text string.
• MID: is the function name to extract characters from the start_num that you specify counting from left to right.
• Text: The text string containing the characters you want to extract.
• Start_num: the ordinal number of the character the MID function starts from.
• Num_chars: Specify the number of characters that LEFT/RIGHT function wants to extract. Num_chars must be greater than or equal to zero. If num_chars is greater than the length of the text, the LEFT/RIGHT function returns the entire text. If num_chars is omitted, it defaults to 1.

For example, you have the string 'Mytour', you extract 5 characters from the left, 5 characters from the right, and 5 characters starting from the 4th character;

=LEFT('Mytour',5) => result is: ThuTh;
=RIGHT('Mytour',5) => result is: em.VN;
=MID('Mytour',4,5) => result is: Thuat.

The TEXT Function

Syntax: = TEXT(number,format)

Where:

• TEXT: is the function name used to change the display format of a number .
• Number: is the number to be formatted.
• Format: the format code you want to apply.

The TEXT function is used to change the format of a number into plain text, or format numbers as dates, percentages, currency, etc.

Below are some common format codes introduced by Software Tips for you:

Above, Software Tips has guided you through some commonly used functions in accounting. Hope this article will be helpful for you. Wish you success!