Count invoices by prefix (COUNTIF wildcards)
The problem
Problem statement
It is month-end close and a supplier export lands with every document type in one column: invoices start with INV-, purchase orders with PO-, returns with RET-. Finance wants only the invoice rows counted. Count how many of A2:A11 are invoices and put the number in Invoice count cell D2.
Use a cell reference to the whole range so the formula still works when next month's export is pasted in.
Problem data
Show the sheet as a table (selectable, copyable)
| A | D | |
|---|---|---|
| 1 | Order ID | Invoice count |
| 2 | INV-1001 | |
| 3 | INV-1002 | |
| 4 | PO-2001 | |
| 5 | INV-1003 | |
| 6 | RET-3001 | |
| 7 | PO-2002 | |
| 8 | INV-1004 | |
| 9 | INV-1005 | |
| 10 | RET-3002 | |
| 11 | INV-1006 |
Notes
COUNTIF takes a range plus a criteria string, and * inside that string matches any run of characters. "INV-*" reads as "starts with INV-". A looser pattern like "*INV*" would also count RET-INV-77, which is exactly the row that separates a careful answer from a fast one. Criteria matching is case-insensitive, so "inv-*" gives the same count.
How to approach
Your answer goes in D2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is D2
- Count over A2:A11
- The prefix you want is INV-, and * stands for 'anything after it'
- The criteria is a text string, so it needs quotes: "INV-*"
Official solutions
Show 2 solutions — try it yourself first
Approach 1: COUNTIF with a prefix wildcard
=COUNTIF(A2:A11,"INV-*")
COUNTIF compares each ID against the text pattern "INV-*": the four literal characters must match at the start, then * absorbs whatever follows. Six of the ten rows are invoices.
Approach 2: SUMPRODUCT over LEFT (no wildcards)
=SUMPRODUCT(--(LEFT(A2:A11,4)="INV-"))
LEFT pulls the first four characters of every ID and compares them with "INV-"; the double minus turns TRUE/FALSE into 1/0 so SUMPRODUCT can add them up. Same count, and useful when the rule is about position rather than pattern.
Function guide
FAQ
How do I count cells that start with a specific text?
Put the prefix and a wildcard in the criteria: =COUNTIF(A2:A11,"INV-*"). The * matches any number of characters after the prefix, so INV-1001 and INV-9 both count.
Why does "*INV*" give the wrong number here?
Because * on both sides means 'contains', not 'starts with'. RET-INV-77 contains INV-, so it gets counted. Anchor the pattern at the start ("INV-*") when the prefix is what defines the type.
Is COUNTIF case-sensitive?
No. "inv-*" and "INV-*" return the same count. If you need case-sensitive counting, use SUMPRODUCT with EXACT instead.