Excel Formulas: SUMIF and VLOOKUP

Excel Sheet Formulas

SUMIF Formula

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

VLOOKUP Formula

• Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Looks up the lookup_value in the first column of table_array and returns the value in the specified col_index_num column
• Example: VLOOKUP(A2, B:C, 2, FALSE) returns the value in column C for the value in cell A2 found in column B

INDEX-MATCH Formula

• Syntax: INDEX(range, MATCH(lookup_value, lookup_array, [match_type])
• Looks up the lookup_value in lookup_array and returns the value at the same position in range
• Example: INDEX(C:C, MATCH(A2, B:B, 0)) returns the value in column C for the value in cell A2 found in column B

Conditional Formatting

• Allows formatting cells based on specific conditions or rules
• Types of rules:
  • Format cells that contain specific values or formulas
  • Format cells that are greater than or less than a certain value
  • Format cells that contain specific text or dates
• Example: Format cells in column A that are greater than 10 with a red fill color

IFERROR Formula

• Syntax: IFERROR(cell, value_if_error)
• Returns value_if_error if the cell contains an error, otherwise returns the value in the cell
• Example: IFERROR(A1/B1, "Divide by zero error") returns "Divide by zero error" if the division results in an error, otherwise returns the result of the division

Excel Sheet Formulas

SUMIF Formula

• The SUMIF formula syntax is SUMIF(range, criteria, [sum_range])
• It returns the sum of cells in [sum_range] that meet the specified criteria in range
• The criteria can be a specific value, a formula, or a reference to another cell

VLOOKUP Formula

• The VLOOKUP formula syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• It looks up the lookup_value in the first column of table_array and returns the value in the specified col_index_num column
• The range_lookup parameter can be set to TRUE or FALSE to specify an exact or approximate match

INDEX-MATCH Formula

• The INDEX-MATCH formula syntax is INDEX(range, MATCH(lookup_value, lookup_array, [match_type])
• It looks up the lookup_value in lookup_array and returns the value at the same position in range
• The match_type parameter can be set to 1, 0, or -1 to specify an exact, exact or approximate, or exact match

Conditional Formatting

• Conditional formatting allows formatting cells based on specific conditions or rules
• Types of rules include formatting cells that contain specific values or formulas, formatting cells that are greater than or less than a certain value, and formatting cells that contain specific text or dates
• Conditional formatting can be used to highlight important data, identify trends, and more

IFERROR Formula

• The IFERROR formula syntax is IFERROR(cell, value_if_error)
• It returns value_if_error if the cell contains an error, otherwise returns the value in the cell
• The value_if_error parameter can be a specific value, a formula, or a reference to another cell