AMITY GLOBAL
BUSINESS SCHOOL N oida
AMITY GLOBAL
BUSINESS SCHOOL N oida
What is a Formula?
• A formula is an expression that performs
calculations in Excel.
• Always starts with =
• Uses operators and cell references
• Example:
• =A1+B1
AMITY GLOBAL
BUSINESS SCHOOL N oida
What is a Function?
• A function is a predefined formula.
• Syntax:
• =FUNCTION(arguments)
• Example:
• =SUM(A1:A5)
AMITY GLOBAL
BUSINESS SCHOOL N oida
Mathematical Functions
• SUM()
• AVERAGE()
• MAX()
• MIN()
• ROUND(), ROUNDUP(), ROUNDDOWN()
AMITY GLOBAL
BUSINESS SCHOOL N oida
Logical Functions
• IF()
• AND()
• OR()
• Example:
• =IF(B2>=40,"Pass","Fail")
AMITY GLOBAL
BUSINESS SCHOOL N oida
Text Functions
• UPPER()
• LOWER()
• PROPER()
• LEFT()
• RIGHT()
• MID()
• LEN()
AMITY GLOBAL
BUSINESS SCHOOL N oida
Date and Time Functions
• TODAY()
• NOW()
• DAY()
• MONTH()
• YEAR()
• DATE()
• DAYS360()
AMITY GLOBAL
BUSINESS SCHOOL N oida
Statistical Functions
• COUNT()
• COUNTA()
• COUNTIF()
• SUMIF()
AMITY GLOBAL
BUSINESS SCHOOL N oida
Lookup Functions
• VLOOKUP()
• HLOOKUP()
• Used to search data in tables
AMITY GLOBAL
BUSINESS SCHOOL N oida
Error Handling
• IFERROR()
• Handles #DIV/0!, #VALUE!, #REF! errors
AMITY GLOBAL
BUSINESS SCHOOL N oida
Applications of Excel Functions
• Marksheet preparation
• Salary calculation
• Sales analysis
• Attendance reports
AMITY GLOBAL
BUSINESS SCHOOL N oida
SUM , SUMIF and SUMIFS
Definition: SUM adds a range of numeric values.
•Syntax: SUM(number1, [number2], …)
Key Characteristics
Aggregates totals (sales, cost, revenue, units).
Ignores text and blank cells.
Foundational for KPIs and financial summaries.
AMITY GLOBAL SUMIF Function
BUSINESS SCHOOL N oida
Definition: SUMIF adds values only when a specified condition is
met.
•Syntax: SUMIF(range, criteria, [sum_range])
Range is the range of cells that you want to evaluate with a criterion.
Criterion is a number, date, or expression that determines whether a
given cell in the sum range is added.
Sum range is the range of cells that are added. If sum range is
omitted, it is assumed to be the same as range
Key Characteristics
Used for conditional aggregation.
Essential in segmentation, performance analysis, and cost control.
AMITY GLOBAL
BUSINESS SCHOOL N oida
SUMIFS-Function
Definition: SUMIFS adds values in a range only when multiple conditions
are satisfied simultaneously.
It is an extension of SUMIF and is used when analysis requires two or more
criteria.
Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],
…)
Managerial Interpretation
SUMIFS enables multi-dimensional decision analysis, such as:
Product profitability by salesperson and period
Post-policy or post-campaign sales tracking
AMITY GLOBAL VLOOKUP and HLOOKUP
BUSINESS SCHOOL N oida
What are Lookup Functions?
•Lookup functions search for a value in a table and return a related
value
Widely used in:
– Sales analysis
– Pricing tables
– Employee/payroll data
– Business dashboards
Common Lookup Functions
•VLOOKUP
•HLOOKUP
•(Modern alternatives: XLOOKUP, INDEX–MATCH)
AMITY GLOBAL VLOOKUP – Vertical Lookup
BUSINESS SCHOOL N oida
VLOOKUP searches for a value vertically (top to bottom) in the first
column of a table and returns a value from another column in the same
row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Lookup value must be in the first column of the table.
Limitations of VLOOKUP
•Cannot look to the left
•Breaks if columns are inserted/deleted
•Slower on large datasets
•Case-insensitive
AMITY GLOBAL HLOOKUP – Horizontal Lookup
BUSINESS SCHOOL N oida
HLOOKUP searches for a value horizontally (left to
right) in the first row of a table and returns a value
from a specified row.
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num,
[range_lookup])
Key Rule
Lookup value must be in the first row.
Arguments
1. lookup_value → value to search
2. table_array → full data table
3. row_index_num → row number to return value from
4. range_lookup → TRUE (approximate) / FALSE (exact)
AMITY GLOBAL
BUSINESS SCHOOL N oida
Difference between Vlookup and Hlookup
Feature VLOOKUP HLOOKUP
Search direction Vertical Horizontal
Lookup in First column First row
Common usage Very high Rare
Data layout Column-based Row-based
AMITY GLOBAL Index Match function
BUSINESS SCHOOL N oida
INDEX Function: Returns the value at a specific position in a range.
Syntax:=INDEX(array, row_num, [column_num])
MATCH Function:Returns the position of a value in a range.
Syntax:=MATCH(lookup_value, lookup_array, match_type)
lookup_value → value to find
lookup_array → where to search
match_type:
0 → exact match (most common)
1 → less than (sorted ascending)
-1 → greater than (sorted descending)
AMITY GLOBAL
BUSINESS SCHOOL N oida
Feature VLOOKUP INDEX–MATCH
Lookup left ❌ No ✅ Yes
Breaks if columns
Yes No
inserted
Requires column index
Yes No
number
Flexible Limited Highly flexible