Buzz
- Syntax: =Vlookup(lookup_value, table_array, col_index_num, [range_lookup])
Where:
+ lookup_value: The value used for searching
+ table_array: The data range for lookup
+ col_index_num: The position of the column to retrieve data from the lookup table.
+ range_lookup: The search range, where TRUE is equivalent to 1 (relative search) and FALSE is equivalent to 0 (absolute search).
Note: For the lookup_value, press F4 three times; for the table_array value, press F4 once.
Significance of using F4
- F4 (1 time): to obtain an absolute value. Absolute means fixing both the column and the row ⇒ $column$row
Example: $A$8 ⇒ fixing column A and fixing row 8
- F4 (2 times): to get a relative column value and an absolute row value - Understood as fixing the row, not fixing the column ⇒ column$row
Example: A$8 ⇒ fixing row 8, not fixing column A
- F4 (3 times): to get a relative row value and an absolute column value - Understood as fixing the column, not fixing the row ⇒ $column$row
2. Countif Function
- Purpose: The Countif function counts the number of cells that meet a specified condition within a given data range.
- Syntax: COUNTIF(Data range, condition)
Where:
+ Data range: The referenced data range
+ Condition: is the referenced condition
3. SUM Function
The Sum function is one of the fundamental calculation functions in Excel. It is used to calculate the sum of a row or column in a data table.
- Purpose: Calculate the sum of numbers
- Syntax: SUM(number1, number2, ...)
Where:
Number1, number2... are the numbers to be calculated
4. SUMIF Function
The Sumif function is similar to the Sum function, but it differs in that it calculates the sum based on a specified condition.
- Purpose: Calculate the conditional sum
- Syntax: SUMIF(range, criteria, sum_range)
Where:
+ Range: The selected area containing the condition cells.
+ Criteria: The condition for executing this function.
+ Sum_range: The area to calculate the sum.
Exercise on calculating travel expenses
We have some predefined tables including the Price list, Travel trips, and Summary table.
First, we use the Vlookup function to calculate food, hotel, and transportation costs for the Travel trips table. Then, we use the Sum function to calculate the total cost.
Step 1: Select cell D6, input the formula as follows: =VLOOKUP($A6,$A$15:$D$19,2,0)*(C6-B6+1) to calculate the transportation cost from 10/7 - 15/7, then press Enter to obtain the result. You can drag down to enable the system to calculate automatically.
Step 2: Perform a similar process for the Hotel cost and Food cost columns using the Vlookup function.
Step 3: Next, calculate the total cost using the Sum function in the Total column. Enter the formula in cell G6 as follows: =SUM(D6:F6) and press Enter to get the result. You can drag down for the system to calculate automatically.
Next, we will perform calculations in the Summary table.
Step 4: Enter the formula in cell G15: =COUNTIF($A$6:$A$11,F15) to calculate the number of trips, then press Enter to complete. Drag down to complete the cells below.
Step 5: To calculate the total food, transportation, and hotel expenses for trips to Da Lat, we use the SUMIF function. Enter the formula in cell H15: =SUMIF($A$6:$A$11,F15,$F$6:$F$11) and press Enter. We will calculate the total food expenses for trips to Da Nang.
The Transportation cost and Hotel cost columns are calculated similarly.
Step 6: Finally, calculate the total amount using the Sum function as in step 3. Enter the formula in cell K15: =SUM(H15:J15).
Scroll down to let the system automatically complete the cells below.
Here is a guide for the travel expense calculation exercise using Vlookup, Countif, Sum, and Sumif. We hope that with this article, you can easily use these functions for travel expense calculations and similar exercises. If you encounter difficulties during the process, feel free to comment below, and the technical team at Mytour will assist you.
