Write reliable formulas
Want more background?
The idea in everyday life
Use the six sample orders to calculate each order’s value, then add only paid orders. Copy formulas and test a changed quantity. You can check each result against the original rows before using the finished sheet.
A formula uses values in cells. When a value changes, its calculated result changes too.
Turn six orders into a checked total
How much money came from paid book orders? Build the answer a step at a time. You can use Excel or Google Sheets.
Open a blank spreadsheet and name its sheet Orders. Copy the table below, click A1 once, and paste. Each header belongs in its own column. Prices are in USD; enter the numbers without typing a dollar sign.
Six orders from a school fundraiser
Paste at A1.
| AOrder | BCategory | CStatus | DUnits | EUnit price |
|---|---|---|---|---|
| 101 | Books | Paid | 2 | 12 |
| 102 | Games | Paid | 1 | 30 |
| 103 | Books | Canceled | 1 | 12 |
| 104 | Books | Paid | 3 | 12 |
| 105 | Games | Paid | 2 | 30 |
| 106 | Books | Paid | 1 | 18 |
Copy as plain text
1. Calculate one order
Type Total in F1. Click F2, type the formula below, and press Enter. The * means multiply.
=D2*E2You should see: F2 shows 24: two books at $12 each.
D2 holds the quantity; E2 holds the price. Referring to cells lets the answer update when either number changes.
2. Copy it to the other orders
Copy F2. Select F3:F7 and paste. Click F3 to inspect its formula: it should be =D3*E3. Keep the canceled order in the table.
You should see: F2:F7 shows 24, 30, 12, 36, 60, 18.
These relative references follow the row. The $12 canceled order still has an order value; the summary will exclude it from paid sales.
3. Add only paid books
Type Category in H1 and Paid total in I1. Type Books in H2 and Games in H3. In I2, enter:
=SUMIFS($F$2:$F$7,$B$2:$B$7,H2,$C$2:$C$7,"Paid")You should see: I2 shows 78: 24 + 36 + 18.
SUMIFS adds F2:F7, but only where B2:B7 matches H2 AND C2:C7 says Paid. All three ranges cover rows 2–7. Quotation marks surround literal text; H2 is a cell reference.
4. Reuse the formula and check the total
Copy I2 into I3. The category reference changes to H3; each range with $ stays fixed. In I4, enter =SUM(I2:I3).
You should see: I3 shows 90 for Games. I4 shows 168 in paid sales. Adding every order instead would give 180.
Here $ locks the following column or row in a formula. It is not a currency symbol. Check the paid total by adding 24 + 30 + 36 + 60 + 18 yourself.
More practice and troubleshooting
Try a canceled order
With D2 restored to 2, change C5 from Paid to Canceled. F5 stays 36, but paid Books falls to 42 and total paid sales to 132. Restore C5 to Paid afterward.
Count instead of add
In I5, this counts paid Books orders: the answer is 3. COUNTIFS counts matching rows; SUMIFS adds their values.
=COUNTIFS($B$2:$B$7,"Books",$C$2:$C$7,"Paid")If the formula does not work
Start with = and use straight quotation marks. If it appears as text, change the cell format to General (Excel) or Automatic (Sheets), then enter it again. Some regional settings use semicolons instead of commas between arguments. A blank price can produce a misleading zero: check that each price is present before trusting the total.
What to finish
The six-order calculation sheet, paid category totals, and a note showing your changed-quantity test.
Watch for this
Copying a formula without checking which references move, or counting canceled orders as paid sales.
How can I tell I understand it?
You can explain each formula in plain language and show a test case that would catch a wrong result.
Quick knowledge check
Answer all 3 questions. Get at least 2 right and the lesson completes automatically.
You’ll get an explanation for each answer. Signed in? Your results save to your account.
Pass the check. We’ll save the progress.
Answer all 3 questions above and get at least 2 right. You can review the explanations and try again.
Go to the knowledge checkOptional course overview video · same video as Lesson 1
Pivot Table Excel Tutorial
From Kevin Stratvert. Watch here or open it on YouTube .
As the video builds calculated fields or source columns, pause to trace which cells change when the formula is copied.Tools, templates, and lesson sources
Tools and templates
Tools open in a new tab so you can keep your place here.
Sources and further reading
Explore the references behind this lesson.
Report a problem with this lesson
Tell us what went wrong. Please leave out personal information and private project contents.