Excel Formulas: SUMIF and VLOOKUP

SUMIF Formula

• Syntax: SUMIF(range, criteria, [sum_range])
• Returns the sum of cells in the specified range that meet the given criteria
• Example: =SUMIF(A1:A10, ">10", B1:B10) sums up values in column B where corresponding values in column A are greater than 10

VLOOKUP Formula

• Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Looks up a value in a table and returns a value from a specified column
• Example: =VLOOKUP(A2, B:C, 2, FALSE) looks up the value in cell A2 in the first column of the range B:C and returns the corresponding value in the second column

INDEX MATCH Formula

• Syntax: INDEX(range, MATCH(lookup_value, lookup_array, [match_type])
• Looks up a value in a table and returns a value from a specified column
• More flexible and powerful than VLOOKUP, especially when working with large datasets
• Example: =INDEX(C:C, MATCH(A2, B:B, 0)) looks up the value in cell A2 in the range B:B and returns the corresponding value in the range C:C

IF Statements

• Syntax: IF(logical_test, [value_if_true], [value_if_false])
• Tests a condition and returns one value if true and another value if false
• Example: =IF(A1>10, "Greater than 10", "Less than or equal to 10") returns "Greater than 10" if the value in cell A1 is greater than 10, and "Less than or equal to 10" otherwise

ARRAY Formulas

• Syntax: =Formula
• Press Ctrl+Shift+Enter to enter the formula instead of just Enter
• Allows for calculations on arrays or ranges of cells
• Example: =SUM(IF(A1:A10>10, A1:A10, 0)) sums up values in the range A1:A10 that are greater than 10
• Note: ARRAY formulas can be slow and may not be suitable for large datasets

SUMIF Formula

• The SUMIF formula returns the sum of cells in a specified range that meet a given criteria.
• The syntax is SUMIF(range, criteria, [sum_range]).
• An example of SUMIF formula is =SUMIF(A1:A10, ">10", B1:B10), which sums up values in column B where corresponding values in column A are greater than 10.

VLOOKUP Formula

• The VLOOKUP formula looks up a value in a table and returns a value from a specified column.
• The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
• An example of VLOOKUP formula is =VLOOKUP(A2, B:C, 2, FALSE), which looks up the value in cell A2 in the first column of the range B:C and returns the corresponding value in the second column.

INDEX MATCH Formula

• The INDEX MATCH formula looks up a value in a table and returns a value from a specified column.
• The syntax is INDEX(range, MATCH(lookup_value, lookup_array, [match_type]).
• The INDEX MATCH formula is more flexible and powerful than VLOOKUP, especially when working with large datasets.
• An example of INDEX MATCH formula is =INDEX(C:C, MATCH(A2, B:B, 0)), which looks up the value in cell A2 in the range B:B and returns the corresponding value in the range C:C.

IF Statements

• The IF statement tests a condition and returns one value if true and another value if false.
• The syntax is IF(logical_test, [value_if_true], [value_if_false]).
• An example of IF statement is =IF(A1>10, "Greater than 10", "Less than or equal to 10"), which returns "Greater than 10" if the value in cell A1 is greater than 10, and "Less than or equal to 10" otherwise.

ARRAY Formulas

• ARRAY formulas allow for calculations on arrays or ranges of cells.
• The syntax is =Formula and requires pressing Ctrl+Shift+Enter to enter the formula instead of just Enter.
• An example of ARRAY formula is =SUM(IF(A1:A10>10, A1:A10, 0)), which sums up values in the range A1:A10 that are greater than 10.
• Note that ARRAY formulas can be slow and may not be suitable for large datasets.

Basic Excel Formulas

• Arithmetic Operations:
  • Use =A1+B1 to add values in cells A1 and B1
  • Use =A1-B1 to subtract the value in cell B1 from the value in cell A1
  • Use =A1*B1 to multiply values in cells A1 and B1
  • Use =A1/B1 to divide the value in cell A1 by the value in cell B1
• Comparison Operators:
  • Use =A1=B1 to check if values in cells A1 and B1 are equal
  • Use =A1>B1 to check if the value in cell A1 is greater than the value in cell B1
  • Use =IF(logical_test, [value_if_true], [value_if_false]) to test a condition and return one value if true and another value if false
  • Use =IFERROR(cell, "error message") to return a value if an error occurs
• Text Functions:
  • Use =LOWER(cell) to convert text to lowercase
  • Use =UPPER(cell) to convert text to uppercase
  • Use =PROPER(cell) to convert text to proper case
• Date and Time Functions:
  • Use =TODAY() to return the current date
  • Use =NOW() to return the current date and time
  • Use =DATEDIF(start_date, end_date, "D") to calculate the number of days between two dates

Functions

• Use =SUM(range) to calculate the sum of a range of cells
• Use =AVERAGE(range) to calculate the average of a range of cells
• Use =COUNT(range) to count the number of cells in a range that contain numbers
• Use =MAX(range) to return the maximum value in a range of cells
• Use =MIN(range) to return the minimum value in a range of cells
• Use =VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) to look up a value in a table and return a value from a corresponding column
• Use =INDEX(range, MATCH(lookup_value, lookup_array, [match_type]) to look up a value in a table and return a value from a corresponding column

Array Formulas

• Use =SUM(IF(range>number, range, 0)) to sum only the values in a range that are greater than a specified number
• Use =INDEX(range, MATCH(MAX(range), range, 0)) to return the value in a range that corresponds to the maximum value
• Array formulas require pressing Ctrl+Shift+Enter instead of just Enter when entering the formula