In today’s business world, accountants rely heavily on Microsoft Excel to manage financial data, prepare reports, and analyze numbers quickly. While anyone can enter data into Excel, mastering the right formulas is what makes an accountant truly efficient and accurate. From simple calculations like SUM and AVERAGE to advanced ones like VLOOKUP, IF, and INDEX-MATCH, these formulas save time, reduce errors, and improve decision-making.
In this guide, we’ll explore the Top 25 Excel formulas every accountant must know, along with practical examples to help you apply them in real-life accounting tasks.
Below, I use a simple sample table called Transactions (Excel Table) with columns:Date | Voucher | Account | Customer | Amount | Tax | Status | Salesperson
Tip: Turn your data into a Table with Ctrl + T and use structured references (e.g.,
Transactions[Amount]).
1) SUM
What it does: Adds numbers.
=SUM(Transactions[Amount])
Quickly totals all amounts.
2) SUMIFS
What it does: Sum with multiple conditions (e.g., date range + account).
=SUMIFS(Transactions[Amount], Transactions[Account], "Sales", Transactions[Date], ">="&DATE(2025,4,1), Transactions[Date], "<="&DATE(2025,6,30))
Use case: Q1 sales only; or sum by customer & status.
3) COUNTIFS
What it does: Counts rows meeting multiple criteria.
=COUNTIFS(Transactions[Status], "Paid", Transactions[Account], "Sales")
How many paid sales invoices?
4) AVERAGEIFS
What it does: Average with multiple criteria.
=AVERAGEIFS(Transactions[Amount], Transactions[Account], "Sales", Transactions[Status], "Paid")
Average paid invoice value.
5) IF
What it does: Logical test → value if true/false.
=IF([@Status]="Overdue","Follow-up","OK")
Flags overdue invoices.
6) IFS
What it does: Chain multiple conditions neatly.
=IFS([@Amount]>=100000,"High",[@Amount]>=25000,"Medium",TRUE,"Low")
7) IFERROR
What it does: Handles errors gracefully.
=IFERROR([@[Amount]]/[@[Tax]],0)
Avoids #DIV/0! when tax is zero.
8) XLOOKUP
What it does: Modern, flexible lookup (left/right, exact by default).
=XLOOKUP("CUST-007", Customers[CustID], Customers[GSTIN], "Not found")
Also return multiple columns by selecting a multi-column return array.
9) VLOOKUP (Legacy but common)
What it does: Vertical lookup (be careful with column index).
=VLOOKUP("CUST-007", Customers!A:H, 5, FALSE)
Prefer
XLOOKUPwhere available.
10) INDEX + MATCH
What it does: Powerful two-step lookup (works leftward; great for 2D lookups).
=INDEX(Rates[Rate], MATCH([@Account], Rates[Account], 0))
Two-way example (row & column):
=INDEX(PivotArea, MATCH("Sales", RowLabels, 0), MATCH("Apr-2025", ColLabels, 0))
11) SUMPRODUCT
What it does: Conditional math without helper columns; weighted averages.
=SUMPRODUCT((Transactions[Account]="Sales")*(Transactions[Status]="Paid")*Transactions[Amount])
Weighted average tax rate:
=SUMPRODUCT(Transactions[Amount], Transactions[Tax]) / SUM(Transactions[Amount])
12) ROUND, ROUNDUP, ROUNDDOWN
What they do: Control rounding for reports, invoices, GST.
=ROUND([@Amount]*1.18, 0) // nearest rupee
=ROUNDUP([@Amount]*1.18, 0) // always up
=ROUNDDOWN([@Amount]*1.18, 0) // always down
13) ABS
What it does: Absolute value—useful for variance and adjustments.
=ABS([@Amount]-[@Budget])
14) EOMONTH & EDATE
What they do: Month math—closing, aging buckets.
=EOMONTH([@Date], 0) // month-end of transaction month
=EDATE([@Date], 3) // +3 months
15) DATE, YEAR, MONTH, DAY
What they do: Build and dissect dates (reporting, grouping).
=DATE(2025,4,1)
=YEAR([@Date]) // 2025
=MONTH([@Date]) // 4
=DAY([@Date]) // 1
16) DATEDIF
What it does: Precise gaps (undocumented but reliable).
=DATEDIF([@JoiningDate], TODAY(), "Y") // years of service
Other units: "M", "D", "YM" (months ignoring years), "MD".
17) NETWORKDAYS / NETWORKDAYS.INTL
What they do: Business days between dates (exclude weekends/holidays).
=NETWORKDAYS([@InvoiceDate], [@DueDate], HolidayList[Date])
NETWORKDAYS.INTL lets you define weekend pattern (e.g., Friday–Saturday).
18) WORKDAY / WORKDAY.INTL
What they do: Add business days to a date (promised date / SLAs).
=WORKDAY([@InvoiceDate], 7, HolidayList[Date]) // due date after 7 workdays
19) TEXT
What it does: Format numbers/dates to text (report labels, exports).
=TEXT([@Date], "dd-mmm-yyyy")
=TEXT([@Amount], "₹#,##0.00")
20) TEXTJOIN / CONCAT
What they do: Build strings (invoice titles, addresses).
=TEXTJOIN(", ", TRUE, [@Customer], [@City], [@State])
Skips blanks with the TRUE argument.
21) FILTER (Dynamic arrays)
What it does: Extract rows matching criteria—live query!
=FILTER(Transactions, (Transactions[Account]="Sales")*(Transactions[Status]="Paid"))
Great for creating dynamic sub-ledgers.
22) UNIQUE
What it does: Distinct lists (customers, accounts) for validation and pivots.
=UNIQUE(Transactions[Customer])
23) SUBTOTAL
What it does: Aware of filters; ignores hidden rows (choose function code 9/109 for SUM).
=SUBTOTAL(109, Transactions[Amount]) // SUM visible only
24) NPV, IRR, PMT (Finance Trio)
What they do: Core finance math for accountants.
- NPV – Net Present Value:
=NPV(10%, C2:C7) + C1
(10% discount rate; C1 is initial outflow if entered as a positive value—add it separately.)
- IRR – Internal Rate of Return:
=IRR(C1:C7)
- PMT – Loan EMI:
=PMT(10%/12, 60, -500000)
(10% annual, 60 months, ₹5,00,000 principal.)
For irregular timings, use XNPV/XIRR.
25) SORT
What it does: Sort ranges dynamically (often used with FILTER/UNIQUE).
=SORT(FILTER(Transactions, Transactions[Status]="Unpaid"), 1, 1)
Sorts by first column ascending.
Practical Mini-Scenarios
A) Aging Bucket (30/60/90+)
=IFS([@DaysDue]<=30,"0–30",[@DaysDue]<=60,"31–60",[@DaysDue]<=90,"61–90",TRUE,"90+")
B) Month-End Provisioning
=IF(EOMONTH([@Date],0)=TODAY(),"Provision","")
C) Sales by Rep (Dynamic report)
=LET(
data, Transactions,
sales, FILTER(data, data[Account]="Sales"),
SUMIFS(sales[Amount], sales[Salesperson], H2)
)
(Using LET to make it readable; optional but powerful.)
Common Pitfalls & Pro Tips
- Dates: Use
DATE(yyyy,mm,dd)inside criteria (avoid text dates). SUMIFStext criteria: Use operators with&→">="&DATE(2025,4,1).- Rounding: Always round before tax filings/exports to prevent paise mismatches.
- Dynamic Arrays: If results “spill,” ensure cells below/right are empty.
- Lookups: Prefer
XLOOKUPwith a clear not-found message:=XLOOKUP(A2, Map[Code], Map[Name], "No match")
Quick Reference (What to use when)
- Conditional totals/counts:
SUMIFS,COUNTIFS,SUMPRODUCT - Lookups:
XLOOKUP(orINDEX+MATCH) - Dates & working days:
EOMONTH,EDATE,NETWORKDAYS,WORKDAY - Cleanup/formatting:
TEXT,TEXTJOIN, rounding functions - Dynamic reporting:
FILTER,UNIQUE,SORT,SUBTOTAL - Finance:
NPV,IRR,PMT
Featured products
-
Best Ergonomic Gaming & Computer Chair
-
HP 15 Laptop – 13th Gen Intel Core i3 (12GB RAM, 512GB SSD)
Original price was: ₹52,721.00.₹33,990.00Current price is: ₹33,990.00. -
Lenovo SmartChoice Chromebook (82UY0014HA) – Compact & Affordable Everyday Laptop
-
Master Excel, Access, Macros & SQL – All in One Course
-
Noise Buds VS102
Original price was: ₹2,999.00.₹799.00Current price is: ₹799.00.
