Excel Masterclass Series
Master Excel
Functions Fast
A complete, structured reference for every Excel function — with syntax breakdowns, real Indian business examples, common mistakes, and use cases for Finance, HR, Audit, Tax, and MIS professionals.
16+
Functions Covered
4+
Function Categories
₹∞
Saved in Excel time
// Total North Zone + Grade A
=SUMIFS(
D2:D100, // Sales
A2:A100, "North",
B2:B100, "A"
)
Result
₹ 22,50,000
All Functions
Math & Trig
Statistical
Lookup & Reference
Logical
Text
Date & Time
Financial
📂 Function Categories
Browse by function type to find what you need quickly
🎯 Recommended Learning Path
Follow this sequence to build Excel skills systematically — from basics to advanced
1
Aggregation Basics
SUM · AVERAGE
COUNT · COUNTA
COUNT · COUNTA
2
Conditional Functions
SUMIF · COUNTIF
AVERAGEIF
AVERAGEIF
3
Multi-Condition
SUMIFS · COUNTIFS
SUMPRODUCT
SUMPRODUCT
4
Lookup & Logic
VLOOKUP · XLOOKUP
IF · INDEX-MATCH
IF · INDEX-MATCH
📚 All Covered Functions
Click any card to access the complete guide with examples, syntax, and tips
=SUM()
Math & Trigonometry
Adds all numeric values in a range — the most fundamental Excel function for totalling sales, expenses, and financial line items.
=SUM(number1, [number2]…)
=SUMIF()
Math & Trigonometry
Sums values matching one condition. Essential for zone-wise sales totals, category-wise expenses, and conditional financial summaries.
=SUMIF(range, criteria, [sum_range])
=SUMIFS()
Math & Trigonometry
Sums values meeting multiple simultaneous conditions. The backbone of MIS dashboards, multi-dimensional P&L, and GSTR reconciliation.
=SUMIFS(sum_range, range1, crit1…)
=SUMPRODUCT()
Math & Trigonometry
Multiplies arrays element-by-element then sums results. Handles weighted averages, conditional aggregation, and unique count tricks in one formula.
=SUMPRODUCT(array1, [array2]…)
=COUNT()
Statistical
Counts cells containing numeric values only. Ignores text, blanks, and errors — ideal for verifying numeric data completeness in registers.
=COUNT(value1, [value2]…)
=COUNTA()
Statistical
Counts all non-blank cells — numbers, text, dates, errors, and logical values. Perfect for headcount, data fill-rate KPIs, and dynamic list sizing.
=COUNTA(value1, [value2]…)
=COUNTIF()
Statistical
Counts cells meeting one condition. Supports text, numbers, wildcards, and comparisons. Powers duplicate detection, result analysis, and status tallies.
=COUNTIF(range, criteria)
=COUNTIFS()
Statistical
Counts cells satisfying multiple conditions simultaneously. Used for multi-dimensional frequency analysis in HR, Sales, and Audit reporting.
=COUNTIFS(range1, crit1, range2…)
=VLOOKUP()
Lookup & Reference
Searches the first column of a table and returns a value from a specified column. The most widely used lookup function across Indian finance teams.
=VLOOKUP(value, table, col, [match])
=XLOOKUP()
Lookup & Reference
The modern replacement for VLOOKUP — searches in any direction, returns arrays, handles missing values natively, and doesn't need column numbers.
=XLOOKUP(lookup, array, return…)
=INDEX(MATCH())
Lookup & Reference
The power combo that overcomes VLOOKUP's limitations — looks up in any direction, doesn't break when columns are inserted, and handles dynamic arrays.
=INDEX(array, MATCH(val, range, 0))
=IF()
Logical
Returns one of two values based on a condition. The foundation of all Excel decision logic — used for pass/fail, slab calculations, and conditional flags.
=IF(condition, value_if_true, value_if_false)
⚡ Quick Reference Table
All functions at a glance — syntax, purpose, and difficulty level
| Function | Purpose | Syntax Pattern | Category | Level |
|---|---|---|---|---|
| =SUM() | Add all numbers in a range | =SUM(A2:A100) | Math & Trig | Beginner |
| =SUMIF() | Sum with 1 condition | =SUMIF(A:A,"North",B:B) | Math & Trig | Beginner |
| =SUMIFS() | Sum with multiple conditions | =SUMIFS(C:C,A:A,"N",B:B,"X") | Math & Trig | Intermediate |
| =SUMPRODUCT() | Multiply arrays then sum | =SUMPRODUCT(B2:B10,C2:C10) | Math & Trig | Advanced |
| =COUNT() | Count numeric cells only | =COUNT(B2:B100) | Statistical | Beginner |
| =COUNTA() | Count all non-blank cells | =COUNTA(A2:A100) | Statistical | Beginner |
| =COUNTIF() | Count cells with 1 condition | =COUNTIF(A:A,"North") | Statistical | Beginner |
| =COUNTIFS() | Count with multiple conditions | =COUNTIFS(A:A,"N",B:B,"X") | Statistical | Intermediate |
| =VLOOKUP() | Vertical lookup in a table | =VLOOKUP(E2,A:C,3,0) | Lookup & Ref | Intermediate |
| =XLOOKUP() | Modern flexible lookup | =XLOOKUP(E2,A:A,C:C,"NA") | Lookup & Ref | Intermediate |
| =INDEX(MATCH()) | Flexible 2-way lookup | =INDEX(C:C,MATCH(E2,A:A,0)) | Lookup & Ref | Advanced |
| =IF() | Conditional true/false logic | =IF(A2>60,"Pass","Fail") | Logical | Beginner |
💡 Pro Tips & Tricks
Time-saving techniques every Excel user in India should know
AutoSum Shortcut
Select a cell below your data and press Alt + = to instantly insert a SUM formula. Works for rows and columns. The fastest way to total a column in Excel.
Alt + = → =SUM(B2:B10)
Lock References with F4
When building SUMIF or COUNTIF formulas to drag down, press F4 to toggle absolute references ($). Locks the data range so it doesn't shift as you copy the formula.
A2:A100 → $A$2:$A$100
Data Fill-Rate Formula
Use this pattern to instantly compute what % of a column has been filled — a must-have KPI for any MIS dashboard or data quality report.
=COUNTA(A2:A100)/ROWS(A2:A100)