Keith’s Training Hub

LESSON 1 OF 16 · ABOUT 15 MINUTES

Count matching orders with COUNTIF

Find how many orders are Open without counting by eye.

Download the practice workbook (.xlsx)Open a copy in Excel and keep it for the next lesson.

Learn the skill

A conditional count answers one clear question about a list. It does not add costs.

Worked example

Orders contains eight fictional training orders. Status is in E2:E9.

COUNTIF checks each status in that range and counts each match for "Open".

It counts 6 Open orders. The other two, TR-102 and TR-106, are Closed.

Follow these steps

  1. On the Summary sheet, select B2.
  2. Enter =COUNTIF(Orders!E2:E9,"Open") and press Enter.
  3. On the Orders sheet, count the six Open entries in column E.
  4. In a spare cell on Summary, enter =COUNTIF(Orders!E2:E9,"Closed"). It should give 2.
  5. Check that the two counts add up to all eight orders.

Try it yourself

Count North team orders using Orders!B2:B9. Explain why this result differs from a cost total.

Check your practical result
  • The North count is 3.
  • Your formula counts labels in the Team column, not amounts.
  • You can explain the range and criterion.

This is your own review, not an automatic inspection of Excel.

If it does not work

If the result is zero, check the spelling, spaces and chosen column. A filter does not make COUNTIF count only visible rows.

Optional knowledge check

This check is for practice. It is not saved or required to continue.

What does COUNTIF count here?

Continue whenever you are ready. No test or recorded score is required.

Course guide and Microsoft helpDownload the whole course guide (.txt)

Steps primarily use Microsoft 365 desktop Excel for Windows. Other versions can differ. Formulas use English names and commas; your region may use semicolons. Work only on the fictional practice copies.

Microsoft: COUNTIF (opens in a new tab). Guidance checked 4 November 2026.

Browser knowledge checks do not execute Excel. This programme does not change your saved progress in other courses.

All 16 lessons and the day programme

Welcome and setup: 09:30–09:40. Evaluation and close: 16:15–16:30.

The four session windows match the supplied programme. Lesson times below are approximate independent practice times. Facilitators can use the remaining time for demonstrations, questions and breaks, including lunch within Session 2.

SESSION 1 · 09:40–11:40

More functions

Count, total, classify and work with dates.

  1. Count matching orders with COUNTIF · 15 min
  2. Add matching costs with SUMIF · 15 min
  3. Use IF to label a result · 15 min
  4. Use TODAY without losing a reporting date · 15 min

SESSION 2 · 11:40–14:30

Managing datasets

Keep records together, find what matters and control new entries.

  1. Sort whole records safely · 15 min
  2. Sort by team, then amount · 15 min
  3. Put priorities in a custom order · 15 min
  4. Filter a list and restore every row · 15 min
  5. Make a safe dropdown with data validation · 20 min

SESSION 3 · 14:30–15:30

Linking

Reuse values across cells, sheets and files with clear checks.

  1. Link cells instead of retyping · 10 min
  2. Link sheets inside one workbook · 10 min
  3. Link a value from another file · 15 min
  4. Inspect, repair and safely remove a link · 15 min

SESSION 4 · 15:30–16:15

Charts

Choose a chart, label it and improve its design.

  1. Choose a chart for the question · 15 min
  2. Label the chart so someone else can read it · 15 min
  3. Improve the design without changing the message · 15 min

Final practical task and evaluation →