Resources | Subject Notes | Information Technology IT
This section covers the use of essential spreadsheet functions: SUM, IF, VLOOKUP, and COUNTIF. These functions are fundamental for data analysis and manipulation within spreadsheets.
The SUM function is used to add a range of numbers.
Syntax: =SUM(number1, [number2], ...)
Example: =SUM(A1:A10) will calculate the sum of the values in cells A1 through A10.
Example: =SUM(A1, B2, C3) will calculate the sum of the values in cells A1, B2, and C3.
Example: A table demonstrating the use of SUM.
| Cell | Value |
|---|---|
| A1 | 10 |
| A2 | 20 |
| A3 | 30 |
| Sum of A1:A3 | =SUM(A1:A3) = 60 |
The IF function performs a logical test and returns one value if the test is true and another value if the test is false.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Example: =IF(A1>50, "Pass", "Fail") will return "Pass" if the value in A1 is greater than 50, and "Fail" otherwise.
Example: =IF(B1="Yes", 1, 0) will return 1 if the value in B1 is "Yes", and 0 otherwise.
Example: A table demonstrating the use of IF.
| Cell | Value | Formula | Result |
|---|---|---|---|
| A1 | 60 | =IF(A1>50, "Pass", "Fail") | Pass |
| A2 | 40 | =IF(A2>50, "Pass", "Fail") | Fail |
| B1 | Yes | =IF(B1="Yes", 1, 0) | 1 |
| B2 | No | =IF(B2="Yes", 1, 0) | 0 |
The VLOOKUP function searches for a value in the first column of a table and returns a value from the same row in a specified column.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments:
lookup_value: The value to search for.table_array: The range of cells containing the table.col_index_num: The column number in the table array from which to return a value.[range_lookup]: Optional. TRUE for approximate match (default), FALSE for exact match.Example: Assuming a table in range C1:D5 with the lookup value in C1 and returning the value from the second column (D1), the formula would be =VLOOKUP(C1, C1:D5, 2, FALSE).
Example: A table demonstrating the use of VLOOKUP.
| Lookup Value (C1) | Table Array (C1:D5) | Column Index (2) | Result |
|---|---|---|---|
| 5 | C1:D5 | 2 | 25 |
| 10 | C1:D5 | 2 | 40 |
| 15 | C1:D5 | 2 | 55 |
The COUNTIF function counts the number of cells within a range that meet a given criteria.
Syntax: =COUNTIF(range, criteria)
Example: =COUNTIF(A1:A10, ">50") will count the number of cells in the range A1:A10 that contain values greater than 50.
Example: =COUNTIF(B1:B20, "Apple") will count the number of cells in the range B1:B20 that contain the text "Apple".
Example: A table demonstrating the use of COUNTIF.
| Range | Criteria | Result |
|---|---|---|
| A1:A10 | ">50" | 3 |
| B1:B20 | "Apple" | 5 |