Download the sample data file from the open sources to apply and practice all these
functions.
1. Conditional formatting, if, countif , sumif ,average ,concat.
Customer Category Amount City
Ravi Electronics 1200 Delhi
Asha Grocery 550 Mumbai
Rohan Clothing 800 Delhi
Divya Electronics 350 Chennai
Priya Grocery 700 Mumbai
Arjun Electronics 1500 Kolkata
1. IF Function
➤ Syntax:
=IF(logical_test, value_if_true, value_if_false)
➤ Example:
Add a new column "Status":
=IF(C2>1000, "High", "Low")
2. COUNTIF Function
➤ Syntax:
=COUNTIF(range, criteria)
➤ Example:
Count how many customers are from Delhi:
=COUNTIF(D2:D7, "Delhi")
3. SUMIF Function
➤ Syntax:
=SUMIF(range, criteria, sum_range)
➤ Example:
Total sales from Electronics category:
=SUMIF(B2:B7, "Electronics", C2:C7)
4. AVERAGE Function
➤ Syntax:
=AVERAGE(number1, [number2], …)
➤ Example:
Find the average sale amount:
=AVERAGE(C2:C7)
5.CONCAT Function (or CONCATENATE in older Excel)
➤ Syntax:
=CONCAT(text1, text2, …)
➤ Example:
Join Customer name and City:
=CONCAT(A2, " - ", D2)
6. Conditional Formatting
➤ Use Case 1: Highlight sales > 1000
1. Select Amount column.
2. Go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than.
3. Enter 1000, choose fill color (e.g., green).
➤ Use Case 2: Color each category differently
1. Select Category column.
2. Use "New Rule > Format only cells that contain > Specific Text".
2. INDEX,MATCH, UNIQUE, IFS, COUNTIFS, SUMIFS, AVERAGEIFS.
Date Region Salesperson Product Units Sold Unit Price Revenue
2025-01-03 North Alice Widget A 12 25 =E2*F2
2025-01-05 South Bob Widget B 8 30 =E3*F3
2025-01-06 East Charlie Widget A 15 25 =E4*F4
2025-01-08 West Dana Widget C 10 35 =E5*F5
2025-01-10 North Alice Widget B 5 30 =E6*F6
2025-01-11 South Bob Widget C 14 35 =E7*F7
2025-01-12 East Charlie Widget A 20 25 =E8*F8
2025-01-14 West Dana Widget B 7 30 =E9*F9
2025-01-15 North Alice Widget C 9 35 =E10*F10
2025-01-16 South Bob Widget A 6 25 =E11*F11
1. INDEX
Purpose: Returns the value from a specific position in a range based on row and column
numbers.
Syntax: INDEX(array, row_num, [column_num])
array: Range of cells to look in.
row_num: Row number in the array.
column_num (optional): Column number (if array has multiple columns).
Example :Get the Salesperson name from the 5th row in the list.
=INDEX(C2:C21, 5)
If your Salesperson column is C2:C21, this returns the name from the 5th cell (i.e., row 6 in the
sheet).
2. MATCH:
Purpose: Returns the position of a value in a row or column.
Syntax: MATCH(lookup_value, lookup_array, [match_type])
lookup_value: What you are trying to find.
lookup_array: The range to search in.
match_type:
o 0: Exact match
o 1: Less than or equal
o -1: Greater than or equal
Example: Find the row number where “Charlie” first appears in the Salesperson column.
=MATCH("Charlie", C2:C21, 0)
If "Charlie" is in cell C4 (3rd in the range), it returns 3.
3. UNIQUE
Purpose: Returns a list of distinct values from a column or row (removes duplicates).
Syntax: UNIQUE(array, [by_col], [exactly_once])
array: The range to extract unique values from.
by_col (optional): TRUE for columns, FALSE for rows.
exactly_once (optional): TRUE to return values that appear only once.
Example: List all unique product names:
=UNIQUE(D2:D21)
Output: Widget A, Widget B, Widget C (just once each).
4. IFS
Purpose: Checks multiple conditions and returns different results for each condition — like
multiple IFs combined neatly.
Syntax: IFS(condition1, value1, condition2, value2, ..., TRUE, default_value).
condition: What you're testing.
value: What to return if condition is TRUE.
Example: Classify performance based on Revenue (column G):
=IFS(G2>500,"Excellent", G2>300,"Good", G2>100,"Average", TRUE,"Low")
Output: Will return "Excellent", "Good", etc., based on the value in G2.
5. COUNTIFS
Purpose: Counts how many rows meet multiple conditions.
Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
criteria_range: The range to apply the condition.
criteria: The condition to check.
Example: Count how many times Alice sold Widget A:
=COUNTIFS(C2:C21, "Alice", D2:D21, "Widget A")
Output: If Alice sold Widget A 3 times, it returns 3.
6. SUMIFS
Purpose: Adds values based on multiple criteria.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range: Cells to sum.
criteria_range: Cells to check conditions.
criteria: Condition to match.
Example:Get total revenue from Widget B sold in the South region:
=SUMIFS(G2:G21, D2:D21, "Widget B", B2:B21, "South")
Output: Sum of revenue values matching both conditions.
7. AVERAGEIFS
Purpose:Calculates the average of values that meet multiple conditions.
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
average_range: Cells to average.
criteria_range: Cells to apply conditions.
criteria: Conditions to meet.
Example:Get average units sold by Charlie for Widget C:
=AVERAGEIFS(E2:E21, C2:C21, "Charlie", D2:D21, "Widget C")
Output: Average of Units Sold for Charlie selling Widget C.
