Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →The most useful Excel formulas for accounting and finance are not necessarily the most advanced. They are the ones that repeatedly reduce manual work while keeping reports reviewable: SUMIFS and COUNTIFS summarize transactions, XLOOKUP retrieves supporting data, IFNA exposes missing matches, EOMONTH controls reporting periods, and PMT, XNPV, and XIRR support finance models.
This guide assumes Microsoft 365, Excel 2021, or Excel 2024 unless noted. The formulas are organized around real workflows—reporting, reconciliation, month-end control, debt schedules, and investment analysis—not as an alphabetical function list.
Start with clean, reviewable source data
Formula quality cannot compensate for unreliable source data. Before building a report, put transactions in an Excel Table with one transaction per row and consistent headers.
| Date | Account | Department | Vendor | Status | Amount | Unique ID |
|---|---|---|---|---|---|---|
| 2026-08-03 | 6100 | Operations | Example Services | Paid | 1250.00 | TX-1001 |
| 2026-08-17 | 6100 | Operations | Example Services | Unpaid | 840.00 | TX-1002 |
Use separate columns for dates, account codes, departments, vendors or customers, statuses, amounts, and identifiers. Amounts should be true numeric values, dates should be true Excel dates, and identifiers should retain leading zeros when those zeros are meaningful. Avoid merged cells, manually inserted subtotals, and inconsistent debit-credit sign conventions inside the raw-data range.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →#1 Best Overall
- FULL HD IPS DISPLAY - Enjoy vibrant, crystal-clear images with 178-degree wide-viewing angles
- AMD RYZEN 3 30 PROCESSOR - Everyday performance you can count on; Multitask, stream, game casually, and edit photos smoothly with responsive power and vibrant HDR visuals
- ENJOY UP TO 14 HOURS AND 15 MINUTES OF BATTERY LIFE - HP Fast Charge restores battery from 0 to 50% in approximately 45 minutes
- AMD RADEON 610M GRAPHICS - Experience smooth entertainment; Built for streaming and multitasking, enjoy realistic visuals and efficient performance for work and play
- STORAGE AND MEMORY - 512 GB PCIe NVMe M.2 SSD offers fast speed and efficient storage; and 8 GB LPDDR5 RAM memory boosts performance with higher bandwidth
Convert the range to a Table with Insert > Table and name it Transactions. Structured references such as Transactions[Amount] are easier to audit and expand automatically when rows are added. A supporting table named ChartOfAccounts might contain Account Code, Account Name, and Category.
The function descriptions and version notes below align with Microsoft’s Excel function catalog and its examples of commonly used formulas.
1. SUMIFS: summarize transactions by multiple criteria
SUMIFS adds values that meet two or more conditions. It is the core formula for account analysis, departmental reporting, vendor spend, invoice status reports, and period-based summaries.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Suppose cell A2 contains an account code, cell B1 contains the reporting month, and cell C1 contains a department:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=SUMIFS(Transactions[Amount], Transactions[Account], $A2, Transactions[Department], C$1, Transactions[Date], ">="&EOMONTH($B$1,-1)+1, Transactions[Date], "<"&EOMONTH($B$1,0)+1)This totals the account’s transactions for the selected department and month. The first date criterion is the first day of the month; the second is the first instant of the following month.
Important details
- Operators such as
>=and<must be inside quotation marks and joined to a cell or formula with&. - Use a less-than next-period boundary rather than
<=EOMONTH(...)when timestamps may be present. A transaction on the last day at 3:00 p.m. is greater than midnight on that date. - Use absolute references for fixed report controls and headers so the formula can be copied safely.
- All criteria ranges must cover compatible rows. Excel Tables reduce the risk of one range being shifted relative to another.
If it fails: a result of zero can mean that amounts or dates are stored as text, not that no transactions exist. Check representative cells with ISNUMBER. Also be cautious with external workbooks: Microsoft documents limitations that can produce #VALUE! when conditional aggregation formulas refer to calculated cells in a closed workbook; see its conditional aggregation and closed-workbook guidance.
2. COUNTIFS: count transactions and exceptions
COUNTIFS counts records meeting multiple criteria. It is useful for unpaid invoices, missing approvals, exception reports, high-value transactions, and control checks.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Count unpaid invoices due by the end of a reporting month:
Recommended Free Tools
=COUNTIFS(Invoices[Status], "Unpaid", Invoices[Due Date], "<="&EOMONTH($B$1,0))Count transactions for an account during a month:
=COUNTIFS(Transactions[Account], $A2, Transactions[Date], ">="&EOMONTH(B$1,-1)+1, Transactions[Date], "<"&EOMONTH(B$1,0)+1)COUNTIFS counts records; it does not add their amounts. Pair it with SUMIFS when a reviewer needs both the number of transactions and their total value. A count can reveal unusual activity even when the total amount appears normal.
For a nonblank condition, use "<>". If you need to search for a literal asterisk or question mark, escape the wildcard with a tilde, such as "~*".
Rank #2
- Intel Celeron N4120: 4 Cores & Threads, 1.1GHz Base Clock, Up to 2.6GHz Boost Clock, 4MB Cache, Intel UHD Graphics 600. The perfect combination of performance, power consumption, and value helps your device handle multitasking smoothly and reliably with four processing cores to divide up the work.
If it fails: check that the criteria ranges cover the same rows and that status values do not contain trailing spaces. A visually identical status such as Unpaid will not necessarily match "Unpaid".
3. XLOOKUP: retrieve account names, rates, and metadata
XLOOKUP searches one range and returns the corresponding value from another. It can retrieve account names, tax rates, exchange rates, cost centers, budget amounts, approval owners, or matching invoice records.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Return an account name from a chart of accounts:
=XLOOKUP([@Account], ChartOfAccounts[Account Code], ChartOfAccounts[Account Name], "Missing account")Retrieve a rate while preserving a visible exception if the currency is absent:
=XLOOKUP(A2, Rates[Currency], Rates[Rate], NA())Compared with many VLOOKUP formulas, XLOOKUP is easier to read, can return values to the left or right of the lookup column, specifies the return range directly, and provides a dedicated not-found argument. Its exact or approximate match mode must still be chosen deliberately.
Lookup controls
- Duplicate keys return the first match by default. That can produce a valid-looking but incorrect result.
- Trailing spaces, number-versus-text differences, and inconsistent identifiers can cause a failed match.
- A blank returned from a matched row is not the same as “not found.”
- Do not use
"0"as a fallback when a missing match should be investigated.
Check for duplicate invoice IDs with:
=COUNTIFS(Transactions[Invoice ID], A2)>1Older Excel fallback: use INDEX and exact-match MATCH:
=IFERROR(INDEX(AccountNames, MATCH(A2, AccountCodes, 0)), "Missing account")Microsoft lists XLOOKUP as an Excel 2021-era function. Its alphabetical function reference also documents the older INDEX and MATCH functions.
4. IFNA and IFERROR: handle errors without hiding control failures
Error handling can make a report readable, but it should not make a control failure invisible. Use IFNA when the expected problem is a missing lookup; use IFERROR only when several error types are genuinely acceptable or have a defined presentation.
=IFNA(value, value_if_na)=IFERROR(value, value_if_error)A lookup-specific formula is:
=IFNA(XLOOKUP(A2, ChartOfAccounts[Account Code], ChartOfAccounts[Account Name]), "Account code not found")IFNA handles only #N/A. IFERROR also catches errors such as #VALUE!, #REF!, and #DIV/0!.
This is often risky:
=IFERROR(complicated_formula, 0)A broken reference, invalid data type, or failed calculation can be converted into a misleading zero. Returning "" can be equally misleading because the cell looks empty even though an exception exists.
Use a separate exception column or control count. For example, count missing account descriptions with:
Free tools Windows power users keep installed
One-click scans. No signup required.
Rank #3
- Stunning 15.6" FHD IPS Display: Experience crisp 1920x1080 resolution on this 15.6 inch laptop with an IPS panel that delivers wide viewing angles and vivid colors. The narrow-bezel design maximizes screen real estate for comfortable viewing on this Win 11 laptop, whether you're studying or working.
- Celeron J4105 Processor & 256GB SSD: Powered by a reliable Celeron J4105 processor paired with 12GB DDR4 memory and a fast 256GB M.2 SSD. This laptop computer supports SSD expansion up to 2TB and TF card expansion up to 1TB, so your storage grows with your needs. Delivers smooth multitasking for daily productivity.
- AI-Powered Win 11 Laptop: Built-in AI features enhance your productivity with smart assistance for writing, summarizing, and task management. Pre-installed with Win 11 and includes Office 365 subscription. This student laptop is backed by 1-year warranty and 24/7 customer support.
- All-Day 7000mAh Battery & 180° Hinge: The high-capacity 7000mAh battery keeps this laptop powered through long classes or meetings. The 180-degree lay-flat hinge lets you share your screen effortlessly during presentations. This durable laptop computer adapts to your dynamic workflow.
- Versatile Connectivity Hub: Equipped with USB 3.2, Type-C, Mini HDMI, and 3.5mm audio jack to connect all your peripherals. Stay online anywhere with high-speed 5G WiFi and Bluetooth 4.2. This college laptop keeps you connected at home, in the library, or on the go.
=COUNTIF(Reporting[Account Name], "Account code not found")Microsoft describes IFERROR in its function category reference.
5. EOMONTH: control month-end and reporting periods
EOMONTH returns the final day of a month before or after a specified number of months. It is useful for month-end reporting, aging schedules, accruals, due dates, rolling periods, and contract schedules.
=EOMONTH(start_date, months)Common examples:
=EOMONTH(A2, 0) // month-end for A2
=EOMONTH(A2, 0)+1 // first day of the next month
=EOMONTH(A2, -1) // end of the prior monthTo test whether a transaction belongs to a reporting month:
=AND(TransactionDate>=EOMONTH(ReportMonth,-1)+1, TransactionDate<EOMONTH(ReportMonth,0)+1)The half-open interval—greater than or equal to the start and strictly less than the next month—also works when dates contain times.
If it fails: confirm that the source is a true Excel date. Check with ISNUMBER(A2). Date-looking text may sort alphabetically, fail a SUMIFS date criterion, or make EOMONTH return an error. Convert it with DATEVALUE, Text to Columns, Power Query, or a controlled import process, and check regional date interpretation. Test February and leap years.
Microsoft documents EOMONTH in its function catalog.
6. ROUND: apply the right financial precision
ROUND changes a calculated value to a specified number of digits. It is relevant to currency, tax, interest, allocations, invoice amounts, and per-unit prices.
=ROUND(number, num_digits)Examples:
=ROUND(A2, 2)
=ROUND(NetAmount*TaxRate, 2)
=ROUND(B2, 4)Showing two decimal places through cell formatting is display rounding; it does not change the stored value. Applying ROUND changes the value used in later calculations. A third concept is posting rounding, where the organization applies its accounting system or policy rules before posting.
Do not automatically round every intermediate calculation. Repeated rounding can create differences in allocations, interest schedules, and reconciliations. Instead, decide where rounding belongs and reconcile the final result to the source system.
Excel’s rounding behavior may differ from a specialized banking or accounting policy, including policies that use “round half to even.” Test negative values and confirm the required tax, currency, and posting precision. Microsoft lists ROUND among its mathematical and trigonometric functions.
Rank #4
- Efficient Performance for Everyday Computing: Powered by Intel N150 processor with up to 3.6 GHz Intel Turbo Boost Technology, 6 MB L3 cache, 4 cores, and 4 threads, this HP laptop delivers responsive performance for web browsing, streaming, document editing, and multitasking. Paired with 4GB LPDDR5 RAM and 128GB UFS storage, it handles daily tasks smoothly. Includes 1-year Microsoft 365 Personal subscription for Word, Excel, PowerPoint, and cloud storage to maximize your productivity.
- 14-Inch HD Micro-Edge Display:Enjoy clear visuals on the 14-inch HD (1366 x 768) anti-glare screen with 250-nit brightness and 62.5% sRGB coverage. The micro-edge bezel delivers a 79% screen-to-body ratio in a compact design. An HP True Vision 720p HD camera with noise reduction and dual-array microphones supports clear video calls, remote work, and online learning.
- Modern Connectivity and Wireless Technology: Stay connected with Wi-Fi 6 (2x2) for faster wireless speeds and Bluetooth 5.4 for seamless pairing with accessories. Versatile port selection includes 1 USB Type-C 10Gbps with DisplayPort 1.2 for external displays, 2 USB Type-A 5Gbps ports for peripherals, 1 HDMI 1.4b port, 1 headphone/microphone combo jack, and 1 multi-format SD media card reader. Connect monitors, transfer files quickly, and expand your workspace with ease.
- All-Day Battery Life and Portable Design: Enjoy up to 11 hours of video playback, 7.5 hours of mixed usage, or 7.5 hours of wireless streaming on a single charge, perfect for students and professionals on the go. Weighing just 3.24 lb and measuring 12.76" x 8.86" x 0.71", this lightweight laptop fits easily in backpacks and bags. The stylish willow green top cover with matte finish and natural silver keyboard deck with vertical brushing pattern offer a modern, professional look.
- AI-Enhanced Productivity: Access Microsoft Copilot instantly with the dedicated Copilot key for faster assistance. AI Noise Reduction filters background sounds and improves voice clarity during calls. Dual speakers provide clear audio, while the full-size natural silver keyboard and HP Imagepad support comfortable typing and navigation.
7. LET: make complex formulas easier to review
LET assigns names to intermediate results. This reduces repeated expressions and can make a material reporting or modeling formula easier for another accountant to inspect.
=LET(name1, value1, calculation_or_name2, [value2, ...], calculation)Here, the reporting boundaries are calculated once and given meaningful names:
=LET(start_date, EOMONTH($B$1,-1)+1, end_date, EOMONTH($B$1,0)+1, account_total, SUMIFS(Transactions[Amount], Transactions[Account], $A2, Transactions[Date], ">="&start_date, Transactions[Date], "<"&end_date), ROUND(account_total, 2))Descriptive names such as start_date, end_date, and account_total communicate intent better than x and y. Keep short formulas short; LET is most useful when an expression is repeated or the intermediate steps clarify the logic.
Because LET is listed by Microsoft as an Excel 2021-era function, use a simpler formula or a helper-column design when the workbook must run in older Excel. Document significant assumptions outside the formula as well as inside it.
8. PMT: calculate periodic loan payments
PMT calculates the payment for a loan or other annuity when payments occur at regular intervals. It is useful for debt schedules, equipment financing, mortgage comparisons, and financing scenarios.
=PMT(rate, nper, pv, [fv], [type])For a $100,000 loan at 8% annual interest, paid monthly over five years:
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problems=PMT(8%/12, 5*12, -100000)The negative principal follows Excel’s cash-flow convention and produces a positive payment. If the principal is entered as positive, the payment generally appears as negative. Either convention can work if it is applied consistently.
rateis the interest rate per payment period. Convert an annual rate for monthly payments, such as8%/12.nperis the total number of payment periods.pvis the present value or principal.fvis the remaining balance or future value; use zero for a fully amortizing loan.typeis0for payments at period-end and1for payments at period-beginning.
If it fails: check that the rate frequency matches the payment frequency and that the signs make economic sense. PMT does not create an amortization schedule and does not automatically account for fees, variable rates, balloon payments, taxes, or irregular dates. For irregular payment dates, build a dated cash-flow schedule and consider XIRR.
See Microsoft’s financial functions reference for PMT and related debt functions.
9. XNPV: value cash flows on actual dates
XNPV calculates net present value when cash flows do not occur at equal intervals. It is appropriate for acquisition models, capital projects, investment schedules, and transactions with uneven funding or exit dates.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallBest Value
- 【Powerful Performance】Equipped with an Intel N150 CPU, featuring up to 4.4 GHz, ensuring efficient and powerful multitasking capabilities.
- 【Versatile Connectivity】Stay connected with multiple ports including USB 3.0 Type-C, USB 3.0 Type-A, and a headphone/mic combo jack, with Wi-Fi and Bluetooth for seamless wireless networking.
=XNPV(rate, values, dates)With a Table named CashFlows:
=XNPV($B$1, CashFlows[Amount], CashFlows[Date])A common investor-perspective convention is to record the initial investment as negative and future proceeds as positive. The values and dates must correspond row by row, and the dates must be valid Excel dates.
Use periodic NPV when cash flows genuinely occur at equal, known intervals. Use XNPV when the actual dates matter. The supplied discount rate is an assumption; XNPV calculates the result at that rate but does not determine whether the rate is appropriate.
If it fails: check for mismatched range lengths, text dates, cash flows before the first date, and inconsistent sign conventions. Document the valuation methodology and discount-rate source rather than presenting the output as an objective valuation.
Microsoft describes XNPV in its financial functions reference.
10. XIRR: calculate returns for irregular cash flows
XIRR calculates an internal rate of return for cash flows occurring on actual dates. It is useful for investment contributions and distributions, project returns, private-equity schedules, capital deployment, and financing arrangements with uneven dates.
=XIRR(values, dates, [guess])For an investment Table containing dated cash flows:
=XIRR(Investment[Cash Flow], Investment[Date])From the investor’s perspective, contributions are normally negative and distributions or proceeds positive. Show the underlying dates and values beside the result so a reviewer can audit the calculation.
IRR is the periodic alternative when cash flows occur at equally spaced periods. Do not compare an XIRR based on actual dates with a periodic IRR calculated from differently structured data without explaining the difference.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
If it fails: #NUM! can mean that Excel did not find a solution. A cash-flow series with multiple sign changes can have multiple mathematically valid IRRs, and the optional guess may affect convergence. A single returned percentage is not automatically the only economically meaningful answer.
Microsoft documents XIRR in its financial functions reference.
Compatibility at a glance
| Function | Main role | Compatibility note |
|---|---|---|
SUMIFS |
Multi-criteria aggregation | Broad support in modern and many legacy Excel versions. |
COUNTIFS |
Multi-criteria counts | Broad support in modern and many legacy Excel versions. |
XLOOKUP |
Modern lookups | Microsoft lists it as available from Excel 2021 onward; use INDEX/MATCH for older installations. |
IFERROR/IFNA |
Error handling | IFNA is limited to #N/A; IFERROR catches several error types. |
EOMONTH |
Month-end dates | Check date parsing and very old installation requirements. |
ROUND |
Financial precision | Use according to the organization’s rounding policy; formatting alone is not rounding. |
LET |
Named intermediate calculations | Microsoft lists it as available from Excel 2021 onward. |
PMT |
Periodic loan payments | Rate must match payment frequency. |
XNPV |
Irregular-date NPV | Use actual dates and consistent cash-flow conventions. |
XIRR |
Irregular-date return | May fail or have multiple solutions for complex cash-flow patterns. |
“Excel” is not one uniform product. Microsoft’s support pages show different applicability across Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and earlier editions. Test the workbook in the version used by its recipients.
Formula selection: when Excel formulas are not the best tool
Use formulas when the output must update in place, the logic should be visible in cells, and the data set is manageable. A PivotTable may be better for rapid exploration, grouping, and drill-down across many dimensions. Power Query may be better when the recurring task involves importing files, cleaning inconsistent source data, combining workbooks, or reshaping data before reporting.
For general-ledger workflows, audit trails, bank feeds, payroll, invoicing, or tax processes, a controlled accounting platform may be a better primary system than a manually maintained spreadsheet. Excel can still be valuable for analysis, forecasting, reconciliations, and management reporting.
Quick Recap
Final review checklist
- Are all source dates true dates rather than date-looking text?
- Are transaction amounts numeric, and are identifiers preserved as identifiers?
- Are debit, credit, inflow, and outflow signs defined consistently?
- Are lookup keys unique, or are duplicate keys explicitly flagged?
- Do
SUMIFSandCOUNTIFSranges cover the same rows? - Are missing matches, blanks, and confirmed zero values distinguished?
- Are errors being handled narrowly rather than hidden with
IFERROR(...,0)? - Are reporting dates, rates, tax assumptions, and payment frequencies stored in visible assumption cells rather than hard-coded?
- Does the rounding approach distinguish display, calculation, and posting rounding?
- Do interest rates match payment frequencies, and do
XNPV/XIRRranges use actual corresponding dates? - Does the report reconcile to an independent control total?
- Has the workbook been tested with missing, duplicate, blank, negative, and malformed values?
- Does the target user’s Excel edition support every formula used?
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
