COUNTIF with a wildcard
Count the cells that end with, start with or contain some text.
Task:
Before a mailing you want to know how many sign-ups used a Gmail address and how many came from your own company domain. Put the Gmail count in B10 and the count of addresses starting with sales@ in B11.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of COUNT, COUNTA
0/4The same formula in the other shapes it takes at work.
- COUNT Functions
Three ways to count, and the difference between them is the whole point.
CoreIntermediate - COUNTBLANK: what is still missing
Count the gaps rather than the entries.
Beginner - COUNTIFS: count with two conditions
How many rows meet this condition and that one.
Intermediate - COUNTIF with a wildcard
Count the cells that end with, start with or contain some text.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | J. Owusu | jowusu@gmail.com |
| 3 | R. Patel | sales@harbourtools.co.uk |
| 4 | L. Novak | lnovak@outlook.com |
| 5 | T. Reyes | treyes.work@gmail.com |
| 6 | M. Byrne | sales@byrneandco.ie |
| 7 | K. Adeyemi | k.adeyemi@gmail.com |
| 8 | S. Lindqvist | office@lindqvist.se |
| 9 | ||
| 10 | Gmail addresses | |
| 11 | Start with sales@ |
What this exercise teachesMay contain the answer
An exact-match COUNTIF on "gmail.com" finds nothing, because no cell holds only that. The asterisk lets the condition describe a pattern — where the known text sits — which is how real lists of emails, codes and references are usually searched.