Count open invoices
How many invoices have the status Open?
Formula: =COUNTIFS(D2:D7,"Open") → 2
Hands-on Excel exercises
Intermediate Excel Skills Test Practice
Practice intermediate Excel skills with an invoice list: multi-criteria totals, status counts, and lookups, with worked answers and checks.
Intermediate tests move from single formulas to questions that need two or three conditions at once: the total for one region, but only for paid invoices; the number of open invoices; the region of a named customer. This is the everyday level of office analysis, and it is where SUMIFS, COUNTIFS, and VLOOKUP earn their reputation.
The live sheet is an invoice register with a customer, region, amount, and status. You are asked for the total of paid East invoices, which needs a sum range and two criteria pairs, all of the same size. The worked answers add a count of open invoices, the open amount, and an exact lookup, so you practice the three patterns that cover most questions at this level.
The usual errors are quiet. A criteria range that is one row shorter, a status typed with a trailing space, or a lookup column index counted from the wrong side all produce plausible numbers. Build the habit of filtering the table by the same conditions and comparing the subtotal. If your result differs, one of the ranges is wrong, not the arithmetic. When you are comfortable here, the advanced page raises the bar to reconciliation and error handling.
Your turn
Your turn: in F2, total the amounts of invoices that are in the East region and have the status Paid.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Region | Amount | Status | ||
| 2 | Acme | East | 420 | Paid | ||
| 3 | Birch | West | 310 | Paid | ||
| 4 | Cato | East | 260 | Open | ||
| 5 | Dune | North | 180 | Paid | ||
| 6 | Elm | East | 150 | Paid | ||
| 7 | Fox | West | 225 | Open |
Not yet solved.
Answer: =SUMIFS(C2:C7,B2:B7,"East",D2:D7,"Paid")
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In F2:F4, calculate each manager's revenue.
- In F2, calculate the total of Emma's deals in Boston.
- In F2:F4, look up the item name for each code in column E.
- In D2:D5, tidy up the names: no extra spaces, and each word capitalized.
- Build a PivotTable: the sum of sales by rep, with the result in F1.
- Build a column chart of revenue by month (A1:B7).
Worked answers
Try the spreadsheet first, then compare your formula and result.
Total the open amount
What is the total amount of invoices with the status Open?
Formula: =SUMIFS(C2:C7,D2:D7,"Open") → 485
Look up a customer's region
Return the region of customer Dune with an exact-match lookup.
Formula: =VLOOKUP("Dune",A2:D7,2,FALSE) → North
Frequently asked questions
What is the first argument of SUMIFS?
The range to add up. The criteria ranges and criteria follow in pairs, and every range must have the same number of rows.
Why would a total come out as zero?
Usually a criteria range is misaligned or the text differs, for example a trailing space. Test one criterion at a time.
Is COUNTIFS the same as COUNTIF?
COUNTIF takes one condition, COUNTIFS takes several. COUNTIFS with one pair gives the same result.
Which page comes after this one?
The advanced page, which adds reconciliation between two lists and safe handling of missing items.