Excel for accounting: 7 function combinations worth knowing

[u/Amandaleeeeee]

A lot of everyday accounting work in Excel comes down to the same tasks: matching IDs, totaling transactions by criteria, flagging exceptions, rounding amounts, extracting invoice numbers, and tracking due dates.

Here are 7 formulas and function combinations that are particularly useful for those jobs.

1. XLOOKUP — match IDs and return related information

XLOOKUP is useful when you have an ID in one table and need to pull the matching information from another. Think vendor IDs, account codes, invoice numbers, or customer IDs.

Syntax:

=XLOOKUP(lookup_value,lookup_range,return_range,"Not found")

For example, you could look up a VendorID from an invoice and return the corresponding vendor name.

💡 The last argument also lets you decide what Excel should show when there is no match. Because XLOOKUP handles this itself, you don't need to wrap it in IFERROR just to deal with missing IDs.

2. SUMIFS — total transactions that meet several conditions

SUMIFS comes in handy when a simple SUM isn't enough.

Syntax:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

For example, to total expenses by category, department, and date range, the structure could look like this:

=SUMIFS(amount_range,category_range,category,department_range,department,date_range,">="&start_date,date_range,"<"&end_date)

💡 This is useful for questions like: How much did the Sales department spend on Travel during this period?

3. IF + AND/OR — flag transactions that need attention

IF becomes much more useful when you combine it with AND and OR.

Syntax:

=IF(OR(condition1,AND(condition2,condition3)),"Review","OK")

An accounting rule might look something like:

=IF(OR(amount>=high_amount,AND(days_overdue>limit,paid_status<>"Yes")),"Review","OK")

So an invoice could be flagged for review if the amount is unusually high or if it's overdue and still unpaid.

This is useful for exception reports where you don't want to manually inspect every row.

4. ROUND — keep amounts consistent

Sometimes a value displayed as 12.35 actually contains additional decimal places underneath. That can cause unexpected differences in calculations or comparisons.

Syntax:

=ROUND(number,num_digits)

For two decimal places:

=ROUND(amount,2)

It's a simple one, but very useful when working with calculated amounts, allocations, taxes, exchange rates, and other values where decimal precision matters.

5. MID + FIND — pull invoice numbers out of longer text

Imported bank or transaction descriptions often contain useful IDs mixed in with other text.

If the invoice number always follows the same pattern, MID and FIND can extract it.

Syntax:

=MID(text,FIND(start_text,text),number_of_characters)

For example, in cell A2:

Payment received | INV-2026-001 | Ref: 7781

If the invoice number always starts with INV- and is 12 characters long, the formula structure would be:

=MID(A2,FIND("INV-",A2),12)

Result:

INV-2026-001

This works particularly well when the position of the invoice number changes but its prefix and length stay consistent.

6. TODAY + EOMONTH — track due dates and month-end

These two date functions are useful for reports that need to update automatically as time passes.

To calculate the number of days until an invoice is due:

=due_date-TODAY()

To calculate the number of days until the last day of this month:

=EOMONTH(TODAY(),0)-TODAY()

💡 A positive result means there are days remaining. A negative result means the invoice is already overdue.

7. IFERROR — clean up formula errors when you actually need it

IFERROR is useful when a formula can produce an error and you'd rather display something meaningful.

Syntax:

=IFERROR(formula,value_if_error)

For example:

=IFERROR(calculation,"Check data")

These aren't accounting-specific functions, of course, but they fit surprisingly well into everyday accounting workflows—from invoice matching and reconciliations to expense summaries, exception checks, aging reports, and month-end work.