Detailed Excel formula for PGDCA

Master Excel with this comprehensive guide to essential Excel formulas, complete with exact syntaxes, practical variations, and real-world examples to boost your spreadsheet productivity.

1. SUM

2. AVERAGE

3. COUNT

4. COUNTA

5. MAX/MIN

6. SUMIF

7. SUMIFS

8. COUNTIF

9. COUNTIFS

10. IF

11. IFS

12. AND/OR

13. IFERROR

14. VLOOKUP

15. HLOOKUP

16. XLOOKUP

17. INDEX

18. INDEX MATCH

19. MATCH

20. TODAY/NOW

21. DATEDIF/DAYS

22. NETWORKDAYS

23. ROUND/ROUNDUP/ROUNDDOWN

24. LARGE/SMALL

25. UNIQUE/FILTER/SORT

26. SORT/FILTER

 

 

1. SUM

Basic Standard Sum

• Syntax: =SUM(range1, [range2], ...)

• Sample Data: A1:A4 contains 10, 20, 30, 40.

• Formula: =SUM(A1:A4)

• Output: 100

Variation A: Summing Disjoint (Non-Contiguous) Ranges

• Syntax: =SUM(range1, range2, [constant1], ...)

• Explanation: Adds values from non-adjacent ranges or individual numbers in a single function call.

• Sample Data: A1:A3 (10, 20, 30), C1:C3 (5, 15, 25).

• Formula: =SUM(A1:A3, C1:C3, 100)

• Output: 205 (60 + 45 + 100)

Variation B: 3D Sum Across Multiple Worksheets

• Syntax: =SUM(First_Sheet:Last_Sheet!Cell_Or_Range)

• Explanation: Sums the same cell or range across multiple consecutive worksheets.

• Sample Data: Cell A1 in Sheet1, Sheet2, and Sheet3 contains 100, 200, and 300 respectively.

• Formula: =SUM(Sheet1:Sheet3!A1)

• Output: 600

2. AVERAGE

Basic Standard Average

• Syntax: =AVERAGE(range1, [range2], ...)

• Sample Data: B1:B4 contains 80, 90, 70, 100.

• Formula: =AVERAGE(B1:B4)

• Output: 85

Variation A: AVERAGEA (Including Logical Values & Text)

• Syntax: =AVERAGEA(range1, [range2], ...)

• Explanation: Unlike standard AVERAGE which ignores text/booleans, AVERAGEA evaluates TRUE as 1, FALSE as 0, and text strings as 0.

• Sample Data (A1:A4): 10, TRUE, "N/A", 30

• Formula: =AVERAGEA(A1:A4)

• Output: 10.25 ((10 + 1 + 0 + 30) / 4)

Variation B: Excluding Zeros from Average Calculation

• Syntax: =AVERAGEIF(range, criteria, [average_range])

• Explanation: Combines average logic with a condition to evaluate only non-zero entries.

• Sample Data (A1:A4): 100, 0, 200, 0

• Formula: =AVERAGEIF(A1:A4, ">0")

• Output: 150 ((100 + 200) / 2)

3. COUNT

Basic Standard Count

• Syntax: =COUNT(range1, [range2], ...)

• Sample Data: C1 = 15, C2 = "Apple", C3 = 42, C4 = [Blank].

• Formula: =COUNT(C1:C4)

• Output: 2

Variation A: Counting Numeric Dates and Times

• Syntax: =COUNT(range)

• Explanation: Excel stores dates and times as numbers, so standard COUNT includes them while ignoring text representations.

• Sample Data (A1:A4): "2026-01-01" (as date number), "Pending" (text), 45.5 (number), "100" (text format)

• Formula: =COUNT(A1:A4)

• Output: 2 (Counts the date and 45.5)

4. COUNTA

Basic Standard Count Non-Empty

• Syntax: =COUNTA(range1, [range2], ...)

• Sample Data: C1 = 15, C2 = "Apple", C3 = 42, C4 = [Blank].

• Formula: =COUNTA(C1:C4)

• Output: 3

Variation A: Counting Non-Empty Rows in Dynamic Columns

• Syntax: =COUNTA(column_range) - header_offset

• Explanation: Determines the number of active entries in an entire column to assist with dynamic range calculations.

• Sample Data (Column A): Header in A1, followed by 12 customer records (A2:A13).

• Formula: =COUNTA(A:A) - 1

• Output: 12

5. MAX and MIN

Basic Standard MAX & MIN

• Syntax: =MAX(range) and =MIN(range)

• Sample Data: D1:D4 contains 12, 85, 4, 50.

• Formula 1: =MAX(D1:D4) $\rightarrow$ Output: 85

• Formula 2: =MIN(D1:D4) $\rightarrow$ Output: 4

Variation A: MAXIFS and MINIFS (Conditional Max/Min)

• Syntax:

• =MAXIFS(max_range, criteria_range1, criteria1, ...)

• =MINIFS(min_range, criteria_range1, criteria1, ...)

• Explanation: Finds the maximum or minimum value meeting specific conditional criteria.

• Sample Data: A1:A4 Category ("Tech", "Tech", "Office", "Tech"), B1:B4 Price (150, 300, 450, 200).

• Formula 1 (MAXIFS): =MAXIFS(B1:B4, A1:A4, "Tech") $\rightarrow$ Output: 300

• Formula 2 (MINIFS): =MINIFS(B1:B4, A1:A4, "Tech") $\rightarrow$ Output: 150

2. Conditional Calculations

6. SUMIF

Basic Standard SUMIF

• Syntax: =SUMIF(criteria_range, criteria, [sum_range])

• Sample Data: A1:A4 Regions ("East", "West", "East", "North"), B1:B4 Sales (100, 200, 150, 300).

• Formula: =SUMIF(A1:A4, "East", B1:B4)

• Output: 250

Variation A: Wildcard Matching (* and ?)

• Syntax: =SUMIF(criteria_range, "text*", [sum_range])

• Explanation: Uses wildcards where * represents any number of characters and ? represents a single character.

• Sample Data: A1:A4 ("Widget Alpha", "Gadget A", "Widget Beta", "Sprocket"), B1:B4 (10, 20, 30, 40).

• Formula: =SUMIF(A1:A4, "Widget*", B1:B4)

• Output: 40 (10 + 30)

Variation B: Dynamic Operator Construction using Cell References

• Syntax: =SUMIF(criteria_range, "operator"&cell_reference, [sum_range])

• Explanation: Concatenates a logical operator (>, <, <>) with a cell reference.

• Sample Data: A1:A4 Values (50, 150, 200, 75), C1 Threshold (100).

• Formula: =SUMIF(A1:A4, ">"&C1)

• Output: 350 (150 + 200)

7. SUMIFS

Basic Standard SUMIFS

• Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

• Sample Data: A1:A4 Regions ("East", "East", "East", "West"), B1:B4 Status ("Paid", "Pending", "Paid", "Paid"), C1:C4 Sales (500, 300, 400, 200).

• Formula: =SUMIFS(C1:C4, A1:A4, "East", B1:B4, "Paid")

• Output: 900

Variation A: Date Range Summing (Between Two Dates)

• Syntax: =SUMIFS(sum_range, date_range, ">=start_date", date_range, "<=end_date")

• Explanation: Restricts a sum to transactions occurring within a specified date window.

• Sample Data: A1:A4 Dates (2026-01-05, 2026-01-15, 2026-02-01, 2026-02-10), B1:B4 Amount (100, 200, 300, 400).

• Formula: =SUMIFS(B1:B4, A1:A4, ">=2026-01-01", A1:A4, "<=2026-01-31")

• Output: 300 (100 + 200)

8. COUNTIF

Basic Standard COUNTIF

• Syntax: =COUNTIF(range, criteria)

• Sample Data: A1:A5 contains "Pass", "Fail", "Pass", "Pass", "Fail".

• Formula: =COUNTIF(A1:A5, "Pass")

• Output: 3

Variation A: Counting Blank and Non-Blank Cells

• Syntax:

• Blanks: =COUNTIF(range, "")

• Non-Blanks: =COUNTIF(range, "<>")

• Explanation: Uses "" to count empty cells or "<>" to count populated cells.

• Sample Data (A1:A5): "Task 1", "", "Task 2", "", "Task 3"

• Formula 1 (Blanks): =COUNTIF(A1:A5, "") $\rightarrow$ Output: 2

• Formula 2 (Non-Blanks): =COUNTIF(A1:A5, "<>") $\rightarrow$ Output: 3

Variation B: Identifying Duplicate Rows

• Syntax: =COUNTIF(expanding_range, current_cell)

• Explanation: Evaluates expanding cell references (e.g., A$1:A1) to flag occurrences greater than 1.

• Sample Data (A1:A4): "Apple", "Banana", "Apple", "Cherry"

• Formula (in Cell B1 copied down): =COUNTIF(A$1:A1, A1)

• Outputs (B1:B4): 1, 1, 2, 1

9. COUNTIFS

Basic Standard COUNTIFS

• Syntax: =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

• Sample Data: A1:A4 Dept ("Sales", "Sales", "IT", "Sales"), B1:B4 Score (80, 50, 90, 85).

• Formula: =COUNTIFS(A1:A4, "Sales", B1:B4, ">=80")

• Output: 2

Variation A: Multiple Numeric Range Checks

• Syntax: =COUNTIFS(range1, ">=min_val1", range2, ">=min_val2", ...)

• Explanation: Counts rows where values fall inside specific numeric boundaries across multiple criteria ranges.

• Sample Data: A1:A4 Age (22, 35, 45, 29), B1:B4 Income (45000, 60000, 85000, 52000).

• Formula: =COUNTIFS(A1:A4, ">=25", B1:B4, ">=50000")

• Output: 3

3. Logical & Conditional Formulas

10. IF

Basic Standard IF

• Syntax: =IF(logical_test, value_if_true, value_if_false)

• Sample Data: A1 = 72.

• Formula: =IF(A1>=50, "Pass", "Fail")

• Output: "Pass"

Variation A: Returning Blank Cells ("") Instead of Zero

• Syntax: =IF(logical_test, "", formula_or_value)

• Explanation: Prevents formulas from displaying zeros when input data is missing.

• Sample Data: Cell A1 = "" (Blank)

• Formula: =IF(A1="", "", A1*1.1)

• Output: "" (Visually blank cell)

Variation B: Nested IF Statements

• Syntax: =IF(test1, value1, IF(test2, value2, value3))

• Explanation: Evaluates multiple conditions sequentially by embedding IF statements within each other.

• Sample Data: Cell A1 = 75

• Formula: =IF(A1>=90, "Top", IF(A1>=70, "Average", "Low"))

• Output: "Average"

11. IFS

Basic Standard IFS

• Syntax: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

• Sample Data: A1 = 85.

• Formula: =IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")

• Output: "B"

Variation A: Handling Fallback/Default Conditions

• Syntax: =IFS(test1, val1, test2, val2, TRUE, fallback_val)

• Explanation: Uses TRUE as the final test condition to catch any inputs that fail earlier tests.

• Sample Data: Cell A1 = "Unknown"

• Formula: =IFS(A1="North", 1, A1="South", 2, TRUE, 0)

• Output: 0

12. Logical Operators: AND & OR

Basic Standard AND & OR

• Syntax:

• =IF(AND(condition1, condition2), value_if_true, value_if_false)

• =IF(OR(condition1, condition2), value_if_true, value_if_false)

• Sample Data: A1 = 85 (Exam), B1 = 90 (Attendance).

• Formula: =IF(AND(A1>=80, B1>=80), "Eligible", "Not Eligible")

• Output: "Eligible"

Variation A: Combining AND with OR in Logic

• Syntax: =IF(AND(cond1, OR(cond2, cond3)), value_if_true, value_if_false)

• Explanation: Evaluates compound conditions where some criteria are mandatory and others are alternative choices.

• Sample Data: Cell A1 = "East" (Region), B1 = "Manager" (Role), C1 = 5 (Years).

• Formula: =IF(AND(A1="East", OR(B1="Manager", C1>=10)), "Eligible", "Ineligible")

• Output: "Eligible"

13. IFERROR

Basic Standard IFERROR

• Syntax: =IFERROR(value, value_if_error)

• Sample Data: A1 = 100, B1 = 0.

• Formula: =IFERROR(A1/B1, "Division Error")

• Output: "Division Error"

Variation A: Nested Fallback Calculations

• Syntax: =IFERROR(primary_formula, IFERROR(secondary_formula, fallback_value))

• Explanation: Executes an alternative secondary lookup or formula if the primary formula generates an error.

• Sample Data: Primary cell A1 = 0, Secondary cell B1 = 10, Total C1 = 100.

• Formula: =IFERROR(C1/A1, IFERROR(C1/B1, 0))

• Output: 10

4. Lookup & Reference Formulas

14. VLOOKUP

Basic Standard VLOOKUP

• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Sample Data (A1:B3): 101|John, 102|Sarah, 103|Mike.

• Formula: =VLOOKUP(102, A1:B3, 2, FALSE)

• Output: "Sarah"

Variation A: Approximate Match Lookup (TRUE / 1)

• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, TRUE)

• Explanation: Locates values in a sorted range that fall within numeric bands.

• Sample Data (A1:B4): 0|0%, 10000|10%, 50000|20%. Lookup value: 35000.

• Formula: =VLOOKUP(35000, A1:B4, 2, TRUE)

• Output: "10%"

Variation B: Dynamic Column Indexing with COLUMN()

• Syntax: =VLOOKUP(lookup_value, table_array, COLUMN(cell_ref), FALSE)

• Explanation: Replaces static column numbers with dynamic functions to allow formulas to be copied horizontally.

• Formula: =VLOOKUP($A2, DataRange, COLUMN(B1), FALSE)

• Output: Dynamically returns column 2, auto-adjusting to column 3 when copied right.

15. HLOOKUP

Basic Standard HLOOKUP

• Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• Sample Data (A1:C2): Row 1 ("Item", "Price", "Stock"), Row 2 ("Laptop", 1200, 15).

• Formula: =HLOOKUP("Price", A1:C2, 2, FALSE)

• Output: 1200

Variation A: Horizontal Row Lookup across Headers

• Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE)

• Explanation: Locates a specific column header horizontally and retrieves data from a designated row beneath it.

• Sample Data (A1:C2): Row 1 ("Q1", "Q2", "Q3"), Row 2 ($100, $200, $300).

• Formula: =HLOOKUP("Q2", A1:C2, 2, FALSE)

• Output: $200

16. XLOOKUP

Basic Standard XLOOKUP

• Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• Sample Data: A1:A3 Names ("John", "Sarah", "Mike"), B1:B3 IDs (101, 102, 103).

• Formula: =XLOOKUP(103, B1:B3, A1:A3, "Not Found")

• Output: "Mike"

Variation A: Searching Bottom-to-Top (Reverse Lookup)

• Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], -1)

• Explanation: Sets search_mode to -1 to locate the last occurrence of an item in a list.

• Sample Data: A1:A4 Customer ("Acme", "Beta", "Acme", "Delta"), B1:B4 Status ("Old", "Active", "Newest", "Active").

• Formula: =XLOOKUP("Acme", A1:A4, B1:B4, "Not Found", 0, -1)

• Output: "Newest"

Variation B: Multi-Column Array Return

• Syntax: =XLOOKUP(lookup_value, lookup_array, multi_column_return_array)

• Explanation: Returns an entire range of multiple columns from a single matching row simultaneously.

• Sample Data: Lookup ID = 101 in A1:A3, returning both Name (B1:B3) and Department (C1:C3).

• Formula: =XLOOKUP(101, A1:A3, B1:C3)

• Output: Spills two values: ["John", "Sales"]

17. INDEX

Basic Standard INDEX

• Syntax: =INDEX(array, row_num, [column_num])

• Sample Data (A1:B2): A1 = "Red", B1 = "Apple", A2 = "Yellow", B2 = "Banana".

• Formula: =INDEX(A1:B2, 2, 2)

• Output: "Banana"

Variation A: Returning an Entire Row or Column Array

• Syntax:

• Entire Row: =INDEX(array, row_num, 0)

• Entire Column: =INDEX(array, 0, column_num)

• Explanation: Setting row_num or col_num to 0 makes INDEX return an entire row or column array for use in other functions.

• Sample Data (A1:C3 Grid): 1|2|3, 4|5|6, 7|8|9.

• Formula: =SUM(INDEX(A1:C3, 0, 2)) (Sums Column 2)

• Output: 15 (2 + 5 + 8)

18. MATCH

Basic Standard MATCH

• Syntax: =MATCH(lookup_value, lookup_array, [match_type])

• Sample Data: A1:A4 contains "Gold", "Silver", "Bronze", "Platinum".

• Formula: =MATCH("Bronze", A1:A4, 0)

• Output: 3

Variation A: Approximate Search Types (1 and -1)

• Syntax: =MATCH(lookup_value, lookup_array, 1)

• Explanation: match_type = 1 finds the largest value less than or equal to the target in an ascending list.

• Sample Data (A1:A4 sorted): 10, 20, 30, 40.

• Formula: =MATCH(25, A1:A4, 1)

• Output: 2 (Position of 20)

19. INDEX MATCH Combination

Basic Standard INDEX MATCH

• Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Sample Data: A1:A3 Employee ID (101, 102, 103), B1:B3 Salary ($50,000, $65,000, $80,000).

• Formula: =INDEX(B1:B3, MATCH(102, A1:A3, 0))

• Output: $65,000

Variation A: Two-Way Matrix Lookup (Row & Column Intersection)

• Syntax: =INDEX(grid_range, MATCH(row_lookup_val, row_range, 0), MATCH(col_lookup_val, col_range, 0))

• Explanation: Uses two MATCH functions inside INDEX to dynamically find both row and column positions.

• Sample Data (A1:C3): Headers B1:C1 ("2025", "2026"), Rows A2:A3 ("Rent", "Utilities"), Values B2:C3 (Rent2025=1000, Rent2026=1200, Util2025=200, Util2026=250).

• Formula: =INDEX(B2:C3, MATCH("Rent", A2:A3, 0), MATCH("2026", B1:C1, 0))

• Output: 1200

5. Date & Time Functions

20. TODAY and NOW

Basic Standard TODAY & NOW

• Syntax: =TODAY() and =NOW()

• Formula 1: =TODAY() $\rightarrow$ Output: 10/04/2026

• Formula 2: =NOW() $\rightarrow$ Output: 10/04/2026 12:00 PM

Variation A: Dynamic Date Arithmetic

• Syntax:

• Add Days: =TODAY() + num_days

• Subtract Time: =NOW() - fractional_days

• Explanation: Performs relative date math by adding or subtracting days, hours, or weeks.

• Formula 1 (Due Date in 30 Days): =TODAY() + 30

• Formula 2 (Time 12 Hours Ago): =NOW() - 0.5

21. DATEDIF and DAYS

Basic Standard DATEDIF & DAYS

• Syntax:

• =DATEDIF(start_date, end_date, "unit")

• =DAYS(end_date, start_date)

• Sample Data: A1 = "2020-01-01", B1 = "2026-01-01".

• Formula: =DATEDIF(A1, B1, "Y")

• Output: 6

Variation A: DATEDIF Interval Units ("YM", "MD", "YD")

• Syntax: =DATEDIF(start_date, end_date, "YM")

• Explanation: Computes partial duration increments while ignoring years or months.

• "YM": Difference in months, ignoring years.

• "MD": Difference in days, ignoring months and years.

• Sample Data: Start = "2024-01-15", End = "2026-03-20".

• Formula 1 ("YM"): =DATEDIF("2024-01-15", "2026-03-20", "YM") $\rightarrow$ Output: 2

• Formula 2 ("MD"): =DATEDIF("2024-01-15", "2026-03-20", "MD") $\rightarrow$ Output: 5

22. NETWORKDAYS

Basic Standard NETWORKDAYS

• Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

• Sample Data: A1 = "2026-03-02" (Mon), B1 = "2026-03-06" (Fri), C1 = "2026-03-04" (Holiday).

• Formula: =NETWORKDAYS(A1, B1, C1)

• Output: 4

Variation A: Custom Weekends with NETWORKDAYS.INTL

• Syntax: =NETWORKDAYS.INTL(start_date, end_date, [weekend_code], [holidays])

• Explanation: Allows specification of non-standard weekend schedules (e.g., Sunday-only or Friday/Saturday).

• Sample Data: Start = "2026-03-01" (Sun), End = "2026-03-07" (Sat). Weekend Code 11 = Sunday only.

• Formula: =NETWORKDAYS.INTL("2026-03-01", "2026-03-07", 11)

• Output: 6

6. Rounding & Statistical Functions

23. Rounding Functions: ROUND, ROUNDUP, ROUNDDOWN

Basic Standard Rounding

• Syntax:

• =ROUND(number, num_digits)

• =ROUNDUP(number, num_digits)

• =ROUNDDOWN(number, num_digits)

• Sample Input: 12.3456

• Formula 1: =ROUND(12.3456, 2) $\rightarrow$ Output: 12.35

• Formula 2: =ROUNDUP(12.341, 2) $\rightarrow$ Output: 12.35

• Formula 3: =ROUNDDOWN(12.349, 2) $\rightarrow$ Output: 12.34

Variation A: Rounding to Multiples/Whole Powers of 10

• Syntax: =ROUND(number, -negative_digits)

• Explanation: Uses negative num_digits arguments to round numbers to tens (-1), hundreds (-2), or thousands (-3).

• Sample Input: 145,870

• Formula 1 (Nearest Hundred): =ROUND(145870, -2) $\rightarrow$ Output: 145900

• Formula 2 (Nearest Thousand): =ROUND(145870, -3) $\rightarrow$ Output: 146000

24. LARGE and SMALL

Basic Standard LARGE & SMALL

• Syntax: =LARGE(array, k) and =SMALL(array, k)

• Sample Data: A1:A5 contains 10, 50, 30, 90, 70.

• Formula 1: =LARGE(A1:A5, 2) $\rightarrow$ Output: 70

• Formula 2: =SMALL(A1:A5, 1) $\rightarrow$ Output: 10

Variation A: Averaging Top N Values Dynamically

• Syntax: =AVERAGE(LARGE(array, {1, 2, ... k}))

• Explanation: Passes an array constant {1,2,3} into LARGE to retrieve the top 3 entries and averages them.

• Sample Data (A1:A5): 50, 90, 70, 100, 80.

• Formula: =AVERAGE(LARGE(A1:A5, {1,2,3}))

• Output: 90 ((100 + 90 + 80) / 3)

7. Dynamic Array Functions (Modern Excel)

25. UNIQUE

Basic Standard UNIQUE

• Syntax: =UNIQUE(array, [by_col], [exactly_once])

• Sample Data: A1:A5 contains "Red", "Blue", "Red", "Green", "Blue".

• Formula: =UNIQUE(A1:A5)

• Output: Spills dynamic array: "Red", "Blue", "Green"

Variation A: Extracting Items That Appear Exactly Once

• Syntax: =UNIQUE(array, FALSE, TRUE)

• Explanation: Sets the 3rd parameter (exactly_once) to TRUE to return only non-repeating items.

• Sample Data (A1:A5): "A", "B", "A", "C", "B".

• Formula: =UNIQUE(A1:A5, FALSE, TRUE)

• Output: "C"

26. FILTER

Basic Standard FILTER

• Syntax: =FILTER(array, include, [if_empty])

• Sample Data: A1:A4 Names ("John", "Sarah", "Mike", "Anna"), B1:B4 Dept ("Sales", "IT", "Sales", "HR").

• Formula: =FILTER(A1:A4, B1:B4="Sales", "None")

• Output: Spills dynamic array: "John", "Mike"

Variation A: Compound Filtering (AND / OR Logic)

• Syntax:

• AND: =FILTER(array, (condition1) * (condition2), [if_empty])

• OR: =FILTER(array, (condition1) + (condition2), [if_empty])

• Explanation: Uses multiplication (*) for AND conditions and addition (+) for OR conditions.

• Sample Data: A1:A4 Name ("Ann", "Bob", "Cal", "Dan"), B1:B4 Dept ("Sales", "IT", "Sales", "IT"), C1:C4 Score (80, 90, 95, 70).

• Formula (Dept=”Sales” AND Score>85):

=FILTER(A1:C4, (B1:B4="Sales") * (C1:C4>85), "None")

• Output: Spills row: ["Cal", "Sales", 95]

27. SORT

Basic Standard SORT

• Syntax: =SORT(array, [sort_index], [sort_order], [by_col])

• Sample Data: A1:A4 contains "Banana", "Apple", "Dragonfruit", "Cherry".

• Formula: =SORT(A1:A4, 1, 1)

• Output: Spills sorted array: "Apple", "Banana", "Cherry", "Dragonfruit"

Variation A: Multi-Column Sorting

• Syntax: =SORT(array, {col_index1, col_index2}, {order1, order2})

• Explanation: Passes array parameters into sort_index and sort_order to sort by primary and secondary columns.

• Sample Data (A1:B3): "East"|200, "West"|100, "East"|400.

• Formula: =SORT(A1:B3, {1, 2}, {1, -1})

• Output: Sorts Column 1 Ascending (“East” first), then Column 2 Descending (400 before 200).

28. SORT / FILTER / UNIQUE Combinations

Basic Standard Combination

• Syntax: =SORT(UNIQUE(array))

• Sample Data: A1:A5 contains "Sales", "IT", "Sales", "HR", "IT".

• Formula: =SORT(UNIQUE(A1:A5))

• Output: "HR", "IT", "Sales"

Variation A: Fully Dynamic Dashboard List

• Syntax: =SORT(UNIQUE(FILTER(array, criteria_range=criteria, [if_empty])))

• Explanation: Filters active records, extracts unique entries, and returns them alphabetically.

• Sample Data: A1:A5 Products ("TV", "Radio", "TV", "Phone", "Radio"), B1:B5 Status ("In Stock", "Out", "In Stock", "In Stock", "In Stock").

• Formula: =SORT(UNIQUE(FILTER(A1:A5, B1:B5="In Stock")))

• Output: Spills array: "Phone", "Radio", "TV"