About this lesson
Wildcards in COUNTIF and SUMIF
In COUNTIF and SUMIF criteria, * matches any number of characters and ? matches exactly one. =SUMIF(A2:A13,"*billing*",B2:B13) adds every line whose description contains "billing", and "licen?e" matches both licence and license.
=COUNTIF(A2:A13,"Cloud hosting*")What you'll practise
Review an energy retailer's IT costs from a messy ledger export: match descriptions that begin with, contain or are spelt two ways, using * and ?.
4 steps · 40 points · the cells outlined in gold below
- COUNTIF
- SUMIF
- wildcards
- * and ?
The data
| A | B | |
|---|---|---|
| 1 | IT opex, Q1 FY26 | Amount ($k) |
| 2 | Cloud hosting - billing | 186.4 |
| 3 | Software licence - payroll | 42.7 |
| 4 | Contractor - billing upgrade | 238.5 |
| 5 | Software License - CRM | 97.3 |
| 6 | Hardware - laptops | 64.8 |
| 7 | Cloud hosting - analytics | 121.9 |
| 8 | Software support - CRM | 33.6 |
| 9 | Telco - mobile plans | 28.2 |
| 10 | Software licence - finance | 75.1 |
| 11 | Contractor - cyber review | 112.4 |
| 12 | Software License - design | 18.9 |
| 13 | Cloud hosting - website | 154.7 |
| 14 | ||
| 15 | Cloud hosting lines | |
| 16 | Billing platform ($k) | |
| 17 | Software licences ($k) | |
| 18 | Licences % of IT opex |