Gridwork guide
Spreadsheet formulas cheat sheet
A formula starts with = and works something out from other cells: =SUM(C2:C6) adds a range, =SUMIF adds the cells that match, =IF chooses between two answers and =XLOOKUP finds a value in a table. Below, each one is worked out on the same five rows of example data.
The example data
Every example below uses this small table of spending, typed into cells A1 to D6. The results were worked out by Gridwork’s own formula engine.
| Item | Category | Amount | Date |
|---|---|---|---|
| Coffee | Food | 3.5 | 2026-11-02 |
| Train | Travel | 12.4 | 2026-11-03 |
| Lunch | Food | 9.8 | 2026-11-03 |
| Book | Fun | 14 | 2026-11-04 |
| Bus | Travel | 2.6 | 2026-11-05 |
Adding up and counting
| Formula | Gives | What it does |
|---|---|---|
=SUM(C2:C6) | 42.3 | Adds the amounts |
=AVERAGE(C2:C6) | 8.46 | The average amount |
=COUNT(C2:C6) | 5 | How many numbers |
=MAX(C2:C6) | 14 | The largest |
=ROUND(AVERAGE(C2:C6),1) | 8.5 | Rounded to one place |
Adding up what matches
| Formula | Gives | What it does |
|---|---|---|
=SUMIF(B2:B6,"Food",C2:C6) | 13.3 | Adds the Food amounts |
=COUNTIF(B2:B6,"Travel") | 2 | How many are Travel |
=SUMIFS(C2:C6,B2:B6,"Travel",C2:C6,">5") | 12.4 | Travel over 5 |
=AVERAGEIF(B2:B6,"Food",C2:C6) | 6.65 | The average Food amount |
Decisions
| Formula | Gives | What it does |
|---|---|---|
=IF(C5>10,"Big","Small") | Big | Big or small |
=IFS(C3>10,"High",C3>5,"Medium",TRUE,"Low") | High | The first test that’s true |
=IFERROR(C2/0,"No answer") | No answer | Something instead of an error |
=AND(C2>1,C2<5) | TRUE | Both true? |
Looking things up
| Formula | Gives | What it does |
|---|---|---|
=XLOOKUP("Book",A2:A6,C2:C6) | 14 | The amount next to Book |
=VLOOKUP("Train",A2:C6,3,FALSE) | 12.4 | The same, the older way |
=INDEX(A2:A6,MATCH(MAX(C2:C6),C2:C6,0)) | Book | The item with the largest amount |
Text
| Formula | Gives | What it does |
|---|---|---|
=CONCAT(A2," (",B2,")") | Coffee (Food) | Joins text |
=TEXTJOIN(", ",TRUE,A2:A4) | Coffee, Train, Lunch | A list with commas |
=UPPER(B3) | TRAVEL | In capitals |
=LEFT(A3,3) | Tra | The first three letters |
=TEXT(C3,"0.00") | 12.40 | A number as text |
Dates
| Formula | Gives | What it does |
|---|---|---|
=TODAY() | 5 Nov 2026 | Today |
=EDATE(D2,3) | 2 Feb 2027 | Three months on |
=EOMONTH(D2,0) | 30 Nov 2026 | The end of that month |
=DATEDIF(D2,D6,"D") | 3 | Days between |
=NETWORKDAYS(D2,D6) | 4 | Working days between |
=WEEKDAY(D2,2) | 1 | Day of the week (Monday is 1) |
Money
| Formula | Gives | What it does |
|---|---|---|
=PMT(5%/12,36,-10000) | 299.71 | Monthly payment on 10,000 over 3 years at 5% |
=FV(4%/12,24,-100) | 2,494.29 | 100 a month for 2 years at 4% |
=NPER(3%/12,-200,0,5000) | 24.28 | Months to save 5,000 at 200 a month |
Tips that save time
- A $ fixes part of a reference when you fill a formula:
$B$1stays put,B1moves. - Text in a formula goes in double quotes:
"Food". Conditions are text too:">5". - Close every bracket you open. Gridwork closes the last ones for you when you press Enter.
- Dates are numbers counted in days, so subtracting two dates gives the days between them.
Questions
What’s the difference between XLOOKUP and VLOOKUP?
VLOOKUP finds a value in the first column of a table and returns one from a column to its right, counted by number. XLOOKUP takes the column to search and the column to return separately, so it can look left, and it finds an exact match unless you ask otherwise. Use XLOOKUP where you can.
Why does my formula show #NAME?
The spreadsheet doesn’t know a name in it: usually a misspelt function, or text that needs quotes round it. In Gridwork, select the cell to see what’s wrong under the formula bar.
Why is 0.1 + 0.2 not 0.3 in some spreadsheets?
Most spreadsheets store numbers in binary, which can’t hold 0.1 exactly, so tiny errors creep in. Gridwork works in exact decimals, so 0.1 + 0.2 is 0.3.