Guide

Spreadsheet formulas that cover most everyday work

Twenty functions, each with an example that was run rather than imagined: the formula, and under it the answer the engine gave back. Three of them differ from Excel, and one of those three returns a wrong number rather than an error.

Deze pagina in het Nederlands

The sheet these examples run against

One project, two tables. Every formula below was executed against exactly this data, and the line under each one is what came back — including the one that comes back wrong.

Orders. Eight rows, five columns, addressed A to E.
Row	Date	Region	Plan	Seats	Amount
1	2026-01-14	NL	Basic	3	120
2	2026-01-28	BE	Pro	1	240
3	2026-02-11	NL	Pro	2	480
4	2026-02-25	DE	Team	4	960
5	2026-03-09	NL	Basic	5	200
6	2026-03-23	BE	Team	2	480
7	2026-04-07	NL	Pro	1	240
8	2026-04-21	DE	Basic	6	240

Plans. A second table in the same project, addressed as Plans.
Row	Plan	Price	Credits
1	Basic	0	200
2	Pro	14	3000
3	Team	24	6000

No header row. The column name is a property of the column, so A1 is the first order and all eight are A1:A8.
A column reference means two things. In [Amount] * 2 it is this row’s cell; inside SUM([Amount]) it is the whole column. Position decides: a function that folds asks for the range.
Every table is a sheet. Plans!A1:C3 is a rectangle in the other table and [Plans]![Price] a whole column of it. Neither is a copy. Semicolons work as well as commas, so SUM(E1:E8; D1:D8) parses.

Typed into a cell, each formula takes a leading =. Set on the column, where the same expression runs down every row, it does not.

SUM and AVERAGE

Both skip blanks and skip text rather than failing on it, so a label sitting in a column of numbers costs nothing. An average over an empty range is #DIV/0! and not zero, which is the difference between nothing yet and nothing sold.

SUM(E1:E8)

→ 2960

SUM([Amount])

→ 2960 — the same eight cells, named

AVERAGE(E1:E8)

→ 370

SUMIF and SUMIFS

SUMIF takes the range you test, the condition, then the range you actually add. Leave the third argument out and it adds what it tested.

SUMIF(B1:B8, "NL", E1:E8)

→ 1040

SUMIFS reverses that: the range you add comes first, then as many range-and-condition pairs as you like. The flip is the commonest reason a working SUMIF returns nonsense once it becomes a SUMIFS. Conditions are plain strings, and they compare dates as dates because dates are ISO text that sorts correctly.

SUMIFS(E1:E8, B1:B8, "NL", C1:C8, "Pro")

→ 720 — the two Dutch Pro orders

SUMIFS(E1:E8, A1:A8, ">=2026-03-01", B1:B8, "NL")

→ 440 — March onwards, Netherlands only

COUNTIF and COUNTIFS

Same criteria language, counting instead of adding. * matches any run of characters and ? exactly one, and case is ignored. AVERAGEIF, MAXIFS and MINIFS take the same shapes.

COUNTIF(C1:C8, "Pro")

→ 3

COUNTIF(C1:C8, "B*")

→ 3 — every plan starting with B

COUNTIFS(B1:B8, "NL", E1:E8, ">200")

→ 2

IF, IFS and IFERROR

Arguments are lazy: a branch you do not take is never evaluated. That is what lets IFERROR catch a fault instead of the whole cell collapsing.

IF([Region]="NL", [Amount]*21%, 0)

→ 100.8 on row 3 — a trailing % divides by a hundred

IFS is a list of condition-and-answer pairs, first hit wins. End it with a literal TRUE; without one, a row matching nothing gets #N/A rather than a blank. IFNA is the narrower catch: it takes #N/A and lets #DIV/0! through.

IFS([Amount]>=500, "large", [Amount]>=200, "medium", TRUE, "small")

→ large on row 4, small on row 1

IFERROR(VLOOKUP("Gold", Plans!A1:C3, 2), 0)

→ 0 — there is no Gold plan

VLOOKUP and XLOOKUP

VLOOKUP searches the first column of a rectangle and returns the n-th column of the matching row, counted from that rectangle’s left edge.

VLOOKUP([Plan], Plans!A1:C3, 2)

→ 14 on row 3 — the Pro price

One difference to write down: the fourth argument is accepted and ignored, and the match is always exact. VLOOKUP("Pro", Plans!A1:C3, 2, TRUE) returns 14, not an approximate match, so the sorted-thresholds trick for banding a score does not band anything here. Use IFS.

XLOOKUP takes the column to search and the column to return as two separate arguments, so nothing counts positions. Its fourth argument is the value to use when there is no match.

XLOOKUP([Plan], Plans!A1:A3, Plans!B1:B3, 0)

→ 14 on row 3

XLOOKUP("Gold", Plans!A1:A3, Plans!B1:B3, 0)

→ 0 — no match, no #N/A, no IFNA needed

Which to reach for. Each wins something.
	VLOOKUP	XLOOKUP
Returns a column left of the key	No.	Yes.
Survives an inserted column	No — the index counts positions.	Yes.
Built-in not-found value	No. Wrap it in IFNA.	Yes, fourth argument.
Several columns from one table	One rectangle, change the index. Shorter.	A new range pair each time. Longer.
Recognised by a colleague	Everyone knows it.	Newer.

INDEX and MATCH

MATCH gives the position of a value in a range and INDEX the value at a position. Together they do what XLOOKUP does, in the shape people wrote before it existed.

MATCH("Team", Plans!A1:A3)

→ 3

INDEX(Plans!C1:C3, MATCH("Pro", Plans!A1:A3))

→ 3000 — the credits on the Pro plan

Here is the one example on this page that returns a plausible wrong number instead of an error. INDEX takes a single index over a range read row by row; Excel’s two-dimensional form does not exist, so the third argument is dropped and the rectangle is flattened.

INDEX(Plans!A1:C3, 2, 2)

→ 0 — not 14. Position 2 of the flattened rectangle.

Give INDEX one column and one position. MATCH likewise takes two arguments and always matches exactly, so a pasted MATCH(x, range, 0) is right and a pasted MATCH(x, range, 1) quietly stops being approximate.

TEXT

TEXT turns a number into a string, which is what you want when a number has to sit inside a sentence rather than in a cell.

TEXT(SUM(E1:E8), "#,##0.00")

→ 2,960.00

TEXT(0.1234, "0.0%")

→ 12.3%

The pattern covers three things: decimals, from the zeros after the point; thousands grouping, from a comma between digit placeholders; and percent. No date patterns, no currency symbols, no colours. It also formats in English convention wherever you are, so the output is 2,960.00 and never 2.960,00.

The date functions

Dates are ISO strings — 2026-01-14 — because that is what a date column stores, it sorts correctly as text, and a round trip through JSON cannot move it a day. An ambiguous date is refused rather than guessed: DATEVALUE("14/01/2026") gives #VALUE! and says why, since being wrong by a month is worse than saying so.

Run against Orders above. A1 is 2026-01-14, A8 is 2026-04-21.
Formula	Result
DAYS(A8, A1)	97 calendar days
NETWORKDAYS(A1, A8)	70 working days, weekends removed
NETWORKDAYS("2026-01-01", "2026-01-31", "2026-01-01")	21 — the third argument is holidays
WORKDAY(A1, 10)	2026-01-28
EOMONTH(A1, 0)	2026-01-31 — and it clamps, so 31 Jan plus a month is 2026-02-28
EDATE(A1, 3)	2026-04-14
DATEDIF(A1, A8, "M")	3 whole months. D, M or Y only
ISOWEEKNUM(A1)	3 — the week number payroll means
YEAR(A1)	2026. MONTH and DAY work the same way
TODAY()	Read when the sheet computes, not a ticking clock

PMT and NPV

PMT is the payment on a loan. The rate is per period rather than per year, so 6% a year paid monthly is 0.06/12, and the answer is negative because it is money leaving. A fifth argument of 1 means paid in advance.

ROUND(PMT(0.06/12, 60, 12000), 2)

→ -231.99 a month on 12,000 over five years; -230.84 paid in advance

NPV discounts a series of cash flows, and the first flow you pass is discounted by one period, so money spent today belongs outside it. Getting that wrong discounts an investment by an extra period and flatters every project you evaluate. Eleven finance functions in all, including FV, PV, NPER, RATE and IRR.

ROUND(NPV(0.1, 3000, 4200, 6800) - 10000, 2)

→ 1307.29 — the 10,000 spent today sits outside

CORREL

CORREL gives the correlation between two ranges, from -1 to 1. It needs at least two pairs, trims the longer range to the shorter, and returns #DIV/0! when either range never changes. SLOPE, INTERCEPT, FORECAST and RSQ take the same pair.

ROUND(CORREL(D1:D8, E1:E8), 3)

→ 0.046 — seats and revenue barely move together

ROUND(CORREL(Plans!B1:B3, Plans!C1:C3), 3)

→ 0.993 — price and included credits move almost as one

The first number is useful precisely because it is boring: here the seat count explains almost nothing about the amount, because the plan sets the price. One formula, and a chart you never have to explain.

What is not here, and why

There are 167 functions. Nine are absent on purpose, and you are told which: the column formula editor says so before you run anything, and a name typed into a cell comes back with the reason rather than a bare #NAME? — being told something was left out reads differently from suspecting a typo.

Where Excel does something this engine will not. Excel wins every row.
Excel	Here	Why
RAND, RANDBETWEEN	Absent.	A cell that changes every render cannot be compared with what it said a second ago.
UNIQUE, SORT, FILTER, TRANSPOSE	Absent.	They write into cells you never selected. That needs a spill model, and half of one is worse than none.
INDIRECT, OFFSET	Absent.	They build a reference out of text while running, so the cycle guard could not see the dependencies first.
Macros and VBA	Absent.	What a macro automates — repeat this over every row — is what a formula column already is.
INDEX with a row and a column	One index only.	Not stated. You get the wrong cell rather than an error.
Approximate VLOOKUP and MATCH	Always exact.	Not stated. The extra argument is ignored.
TEXT with dates, currency and colours	Decimals, thousands, percent.	Anything more needs a full formatter.
A million rows a sheet	About 50,000 comfortably.	At 100,000 browser storage refuses the write.

Names are English only, so SOM returns #NAME? even though the semicolon a Dutch Excel puts between arguments is fine. A formula column may reference another; a cell asked for while already computing returns #CYCLE! rather than hanging the tab, and that guard is only honest because INDIRECT is absent. The full head-to-head is on how this compares with Excel, and there is using these in a thesis results chapter and the numbers behind a pitch deck.

Questions people ask

What's the difference between VLOOKUP and XLOOKUP?

XLOOKUP takes the column you search and the column you want back as two separate arguments, so nothing counts positions and an inserted column breaks nothing. Its fourth argument is the value to show when there is no match. VLOOKUP counts columns inside one rectangle and can only look right. Here, both match exactly.

Why does my formula show #REF! or #N/A?

#REF! means the address points outside the table, or names a table this project does not have. #N/A means a lookup ran and found nothing: wrap it in IFNA, or give XLOOKUP a fourth argument. There is also #CYCLE!, for a formula that ends up asking for itself.

How do I add up only some of the rows?

SUMIF for one condition, SUMIFS for several. Watch the order, because it flips: SUMIF takes the range you add last, SUMIFS takes it first. Criteria are plain strings and work on dates too, so >=2026-03-01 against a date column adds March onwards.

Do Excel formulas work in Tougather?

Most of them. There are 167 functions, and nine are absent on purpose: RAND, RANDBETWEEN, UNIQUE, SORT, FILTER, TRANSPOSE, INDIRECT, OFFSET and macros. Three behave differently rather than being missing — VLOOKUP and MATCH always match exactly, INDEX takes one index rather than a row and a column, and TEXT covers decimals, thousands and percent only. Names are English, but a semicolon between arguments works as well as a comma.

Paste a CSV or an .xlsx file, put one of these on the column rather than in a cell, and the same expression runs down every row. That is what a macro would otherwise have been for.

Open a spreadsheet

Back to the homepage