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 |