Library › Coding AI › Office and admin

Total room booking hours by month with SUMIFS

Practice: Giving the right context. About 5 minutes.

About this exercise

On your Summary sheet, cell A2 holds a room name and cell B1 holds the first day of a month (like 3/1/2026). Write the formula for Summary!B2 that totals Bookings hours for the room in A2 within B1's month; you'll copy it down to B3:B6 for the rooms in A3:A6, so lock B1. No typed-in room names or dates. One formula in one code block (one line), then under 20 words outside the code.

Common mistakes when you ask AI for this

  • Not saying where the inputs live. The AI types "Room B" and the dates straight into the formula, so it breaks for every other room and month. Try: Add: "Room name is in A2, first day of the month is in B1. No typed-in names or dates."
  • Forgetting you'll copy it down. The AI leaves B1 unlocked, so the next row points at B2 and the totals go wrong. Try: Add: "I'll copy it down to B6, so lock B1 as $B$1."
  • Getting a menu of options. The AI likes to offer two or three versions of the formula, so you're left to pick and test them yourself. Try: Add: "Give one formula only, in one code block, no alternatives."
  • Letting the code sprawl. The AI pads the formula with extras nobody asked for, so it grows long and harder to check. Try: Add: "At most 1 lines of code."
  • Getting a tutorial. By default the AI explains every part of the code at length, which you then have to scroll past. Try: Add: "After the code, one line under 12 words. Nothing else."

What a strong prompt includes

  • Says where the room and month are
  • Mentions copying it down
  • Asks for the month's date range
  • Asks for one answer only
  • Limits the explanation

Practice this now ↑

Related exercises