Skip to content
Spreadsheet teacherBusisiwe Khoza
Subscribe
Subscribe

A free weekly puzzle

One spreadsheet challenge every Sunday, and the lessons to match

A free letter every Sunday: one spreadsheet challenge with three hints, and last week’s worked in full.

Nomvula standing beside her tidy home-office desk.
  • No. 64
  • Medium
  • 10 minutes
  • Any spreadsheet app

Total only the unpaid invoices

Twelve invoices sit in rows 2 to 13. The amount is in column B and the status, Paid or Unpaid, is in column C. Five are unpaid, and their amounts are 120, 340, 85, 410 and 285. Write one formula that totals only the unpaid invoices, without adding by hand. What does it return?

Work it out here

  • Hint 1

    Say in words what you want to add up, and under which condition.

  • Hint 2

    Look for a function that adds a cell only when another cell passes a test.

  • Hint 3

    It needs three things: the cells to test, the test itself and the cells to add.

Show the answer

=SUMIF(C2:C13,"Unpaid",B2:B13) returns 1,240. It checks column C for the word Unpaid and adds the matching amounts from column B.

Last week’s puzzle

Count the people who said yes

Twenty replies sit in A2 to A21, each one Yes, No or Maybe. Nine say Yes. Write a formula that counts the Yes replies, and another that counts every reply that is not Yes.

  1. The steps 1

    Say it in words

    I want to count the cells from A2 to A21 that say Yes.

  2. The steps 2

    Pick the function

    COUNTIF counts the cells in a range that pass one test.

  3. The steps 3

    Write the first formula

    =COUNTIF(A2:A21,"Yes") returns 9.

  4. The steps 4

    Count the rest

    =COUNTIF(A2:A21,"<>Yes") counts every cell that is not Yes, and returns 11.

  5. The steps 5

    Check it

    9 plus 11 is 20, the same as the number of replies. The two formulas agree.

A monitor showing a blurred grid of coloured cells.

Every week

Past puzzles

A small recording nook with a microphone and a sticky-note wall.

About me

I worked in offices for many years as the person people came to when a formula would not work. I loved that, and I noticed how often the problem was small: a missing bracket, a date that was really text, a column nobody had named. Almost nobody had been taught the basics. They had been handed a sheet and told to get on with it.

So I teach the basics, slowly and without jargon. Every Sunday I send one challenge with three hints, and the following week I work the answer through in full. The templates, the cheat sheet and the workbooks are the ones I use with my own students, and the two video courses are for anyone who wants to feel at home in a spreadsheet.

Downloads, a course and merch

Shop