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 XLOOKUP where 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).
• SUMIFS text 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 XLOOKUP with 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 (or INDEX+MATCH)
• Dates & working days: EOMONTH, EDATE, NETWORKDAYS, WORKDAY
• Cleanup/formatting: TEXT, TEXTJOIN, rounding functions
• Dynamic reporting: FILTER, UNIQUE, SORT, SUBTOTAL
• Finance: NPV, IRR, PMT

---