Library › Coding AI › Small business owners

Count unique customers in a month with one formula

Practice: Giving the right context. About 5 minutes.

About this exercise

Write one Google Sheets formula for Summary!B2 that counts unique customers who ordered in the month whose first day is in Summary!$B$1. Orders are on the Orders tab: dates in column A, emails in column C from row 2 down. Customers type their email differently each time, so match emails lowercased and trimmed. Show 0, not an error, for a month with no orders. One formula in one code block, then under 25 words outside the code.

Common mistakes when you ask AI for this

  • Not mentioning messy emails. The AI counts Marisol.Vega@ and marisol.vega@ as two customers. Try: Add: "Match emails lowercased and trimmed."
  • Not saying where the month is. The AI types October into the formula or uses this month. Try: Add: "The month's first day is in $B$1."
  • Forgetting empty months. FILTER errors when nothing matches, and COUNTA of an error shows 1. Try: Add: "Show 0 for a month with no orders."
  • 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."
  • 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 10 words. Nothing else."

What a strong prompt includes

  • Handles messy emails
  • Points at the month cell
  • Handles empty months
  • Asks for one answer only
  • Limits the explanation

Practice this now ↑

Related exercises