Why English? | Asking COUNTIF for a Literal Asterisk Instead of a Wildcard Pattern

An asterisk in an Excel label can be an ordinary character you want to count. An asterisk in COUNTIF’s criteria can instead be an instruction to match a changing sequence of characters. English matters because “count this exact label” and “count labels beginning with this text” are different requests, even when the formulas look nearly the same.

Students searching for COUNTIF wildcard, count a literal asterisk in Excel or count cells containing an exact label need to connect the spreadsheet syntax with the question they actually mean. A formula can return a perfectly explainable number while answering a broader question than the assignment asks.

This guide uses six original fictional labels and a declared matching model grounded in Microsoft’s current documentation. No real workbook is opened or changed, and no live Excel test is claimed. We will predict which labels match, compare the resulting sets and write a clear report of what the count establishes. The learning value lies in understanding the criterion, not merely copying a formula.

Begin by stating the question without formula syntax

Imagine a practice list whose organiser uses Draft* as a particular label. In this invented convention, the asterisk is part of the label’s spelling. It is not an instruction to the reader, a footnote marker or a universal meaning attached to every draft. Our first question is: how many supplied cells contain that exact label?

A different question would be: how many supplied labels begin with Draft and then have any additional characters, including no extra characters? Both questions are reasonable. They need different criteria because one treats the asterisk literally while the other uses a pattern.

Write the intended question before looking at the formula. This gives the learner a stable target against which to judge the result. Otherwise, a plausible number can quietly redefine the task: “The formula returned five, so five must be what we wanted.”

For a school project, a useful instruction might say, “Count the label spelled D-r-a-f-t followed by one literal asterisk in the six supplied cells.” That is deliberately explicit for learning. Once the distinction is shared, ordinary wording such as exact label can carry the same intended boundary.

Back to contents

What Microsoft’s COUNTIF guidance supports

Microsoft’s official COUNTIF guide, checked on 19 October 2026, describes a range and a criterion. It explains that an asterisk can match a sequence of characters, a question mark can match one character and a tilde before an asterisk or question mark makes that special character literal. It also notes that COUNTIF text matching is not case-sensitive.

Those documented rules give us a technical way to express our two requests. They do not decide which request the organiser intended. The student must read the task, understand the supplied label convention and choose the criterion accordingly.

Our worked results below are predictions from explicit fictional strings under those stated rules. They are not screenshots, observed application outputs or a guarantee about every messy imported file. Actual data can contain spaces or other characters that need investigation through an authorised process.

Use the applicable official instructions for the version and locale in front of you. The formulas here use the comma-separated syntax shown in the cited English-language guide. Do not change settings, shared records or formulas in somebody else’s workbook simply to reproduce the paper exercise.

Back to contents

Meet the six original labels

Suppose the range A2:A7 contains these six text values. There are no leading or trailing spaces, hidden characters or missing cells in this declared model. The symbols shown are actual characters in the supplied values, not footnote references to material elsewhere in this article.

Cell	Original text value	Detail worth preserving
A2	Draft*	One literal asterisk follows Draft
A3	DraftA	One letter follows Draft
A4	Draft	Nothing follows Draft
A5	Draft?	One literal question mark follows Draft
A6	Ready	A different word
A7	Draft**	Two literal asterisks follow Draft

Reading the values carefully is part of the exercise. A2 and A7 are not identical. A3 and A5 both have one character after Draft, but those characters differ. A4 is a complete supplied value rather than a missing answer.

The model’s clarity lets us inspect the matching rule without guessing about data cleanup. A real sheet may require additional checks. Here, we know exactly which six strings the criterion will be asked to compare, so every predicted match can be explained individually.

Back to contents

The unescaped asterisk answers a broader question

Consider =COUNTIF(A2:A7,"Draft*"). In its criteria, the asterisk acts as a wildcard. It does not ask only for the one-star value in A2. Under our declared model, it matches A2, A3, A4, A5 and A7: five supplied cells.

A4 is especially useful. The wildcard permits an empty sequence after Draft, so the value Draft also matches. A learner who expects the asterisk to demand at least one extra character would miss that boundary. The pattern is broader than “Draft followed by one visible mark.”

A6 does not match because Ready does not begin with the required Draft text. The matching set is therefore specific, not random. If the organiser wanted all labels beginning with Draft, this criterion could fit the question in our supplied data.

The mistake would be calling that five-cell result the count of the exact one-star label. The formula and its output can be mechanically consistent while the explanation attached to them is wrong. English helps the learner state which question was actually answered instead of treating every count as interchangeable.

Back to contents

Escaping the asterisk expresses the literal request

Now consider =COUNTIF(A2:A7,"Draft~*"). The tilde before the asterisk changes that character’s role in the criterion. Under the documented rule and our clean supplied strings, the pattern asks for Draft followed by a literal asterisk, with no extra unmatched characters.

Only A2 matches. A7 contains two asterisks, so it is not the same complete label. A3 ends in A, A4 has no following character, A5 ends in a question mark and A6 is Ready. The predicted result is one matching cell.

The tilde is not added to the stored label in this exercise. It is part of the criterion’s notation. The cell still contains Draft*, not Draft~*. This distinction between data and instructions is central: a character used to express a matching rule need not be part of the value being matched.

Did you know? Adding a character to a criterion can make its matching set smaller. Here, the tilde makes the intended asterisk literal, reducing five matches to one. The useful explanation concerns the character’s role, not a general rule that longer formulas always give narrower or better answers.

Back to contents

A question mark offers a different wildcard test

Consider =COUNTIF(A2:A7,"Draft?"). Here the question mark matches exactly one character after Draft. In our model, A2, A3 and A5 match. Each contains one additional character: an asterisk, A or a question mark. The predicted count is three.

A4 has no extra character, so it does not meet this pattern. A7 has two extra characters, so it also does not meet it. The criterion therefore differs from both the broad asterisk pattern and the exact literal-asterisk request.

The matching character can itself be a visible punctuation mark in the data. The criterion’s question mark does not demand a letter specifically. In this supplied string model, it asks for one character. That is why the values ending in asterisk and question mark can both match it.

If the task asks for the exact question-mark label instead, use the separate literal criterion Draft~?. In our six values, only A5 matches that criterion. Once again, the learner needs to interpret the request before deciding whether a symbol is ordinary data or part of a pattern instruction.

Back to contents

Compare the criteria by their matching sets

A count is easier to trust when you can explain which supplied cells belong to it. Here is the comparison for the original range. The fifth criterion combines an escaped literal asterisk with a later wildcard; its two roles will be unpacked next.

Criteria text	Meaning in this model	Matching cells	Count
Draft*	Draft followed by any sequence, including none	A2, A3, A4, A5, A7	5
Draft~*	Exact one-star label	A2	1
Draft?	Draft followed by exactly one character	A2, A3, A5	3
Draft~?	Exact question-mark label	A5	1
Draft~**	Draft, then a literal star, then any sequence	A2, A7	2

Two criteria return one in this example, but they do not identify the same cell. Draft~* identifies A2; Draft~? identifies A5. Equal totals do not establish equal meaning or equal matching sets.

Ask a learner to name the set before the number. That simple ordering makes the count accountable to the supplied data. It also prevents a familiar-looking answer from concealing that the wrong label was counted.

Back to contents

Read a mixed literal-and-wildcard criterion in parts

The criterion Draft~** can look confusing because two asterisks appear near each other. Read it by roles: Draft is the fixed text, the tilde makes the next asterisk literal, and the remaining asterisk acts as a wildcard for what follows.

In our range, A2 contains the required literal star and then nothing further; A7 contains the required literal star and then another star. Both match. A3 and A5 lack the required literal star at that position, so their single-character suffixes do not help.

This is not an invitation to make formulas complicated for their own sake. The mixed criterion is useful only when the real question needs that boundary. If the task asks for the exact one-star label, the simpler escaped criterion already expresses the intended request.

The English skill is decomposing an instruction without losing its order. Which character is fixed? Which one changes the role of the next? Which part remains open? A learner who can answer those questions can explain the criterion instead of memorising a visually similar formula and hoping it applies.

Back to contents

A different question: a literal star anywhere in the label

The original exact-label request fixes the whole value as Draft*. Now ask a genuinely different question about the same six clean strings: which supplied labels contain at least one literal asterisk anywhere? We can derive a criterion from the same documented wildcard and escape rules without pretending that the new question is identical to the first.

The criterion *~** has three parts. Its opening asterisk permits a sequence before the required literal star. The tilde makes the next asterisk that required literal character. Its final asterisk permits a sequence afterward. The criterion does not require the word Draft at the beginning, even though both matching values in our original data happen to contain that prefix.

In A2:A7, this predicts matches for A2 and A7, giving two cells. A2 has one star and A7 has two; each contains at least one. The count is not three stars. COUNTIF counts matching cells in the range under this criterion, so a cell with two stars is still one matching cell in this exercise.

That last distinction deserves a careful report: “Two supplied cells contain a literal asterisk.” The sentence “There are two asterisks” would describe a character count and would be false for these supplied values, which contain three star characters altogether. A correct matching rule can therefore still be paired with an incorrect noun in the final sentence.

For a separate counterexample, imagine replacing A6 with the clean text *Ready. The anywhere-star criterion would then match A2, A6 and A7. The criterion Draft~** would still match only A2 and A7 because it requires Draft before the literal star. The original exact Draft~* criterion would still match only A2.

This one changed value reveals a difference that the original equal two-cell results concealed. *~** and Draft~** happen to return the same set in the original six values, but they express different boundaries. A useful check is to ask what plausible supplied string would make their results diverge. The invented *Ready value provides precisely that test without claiming an observed workbook change.

Do not substitute this anywhere-star criterion for the exact-label formula simply because both concern asterisks. State the question first: exact whole label, fixed prefix followed by a star, or literal star somewhere. The three questions overlap in our data, yet they remain different requests. English makes those boundaries visible before the formula compresses them into notation.

Back to contents

Remove one value on paper and explain which counts change

Another way to check understanding is to remove A7, the two-star value, from the supplied matching universe on paper. Do not delete a real row or infer any workbook operation from this exercise. We are simply comparing the original six strings with a new five-string set that excludes Draft**.

The broad Draft* pattern loses one match, falling from five to four. The literal one-star criterion remains one because A7 never belonged to its original matching set. The one-character Draft? criterion remains three because two extra characters did not satisfy it. The mixed Draft~** criterion falls from two to one.

These predictions follow from membership, not from a general assumption that every count must fall when the supplied universe becomes smaller. A count falls only if a removed value belonged to that criterion’s matching set. This is why naming the members before counting them is such a powerful diagnostic habit.

Ask the learner to explain the unchanged literal-star result in a complete sentence. “The exact count stays one because the removed two-star value was not an exact one-star match” connects the rule, value and result. “It stays one because that is the formula answer” does not reveal whether they understand the boundary.

The changed-universe exercise also helps a reviewer preserve a successful check. If a student predicts four for the broad pattern and three for the one-character pattern, those results can be right even if another prediction is wrong. Review each criterion against its own membership rule rather than treating all five answers as one inseparable calculation.

In a report, attach the universe to the result: “Among the five supplied values after excluding the two-star label, four match the broad Draft-prefix pattern.” The qualification matters because it prevents a reader from comparing that four with the original six-cell count as though both results used exactly the same input. This is practice in clear reporting, not permission to remove inconvenient real records.

Keep an unambiguous note of which supplied universe you used. A reviewer should be able to reconstruct the matching set without guessing whether the two-star value was included. That small reporting habit makes a later comparison meaningful: different counts may reflect different criteria, different inputs or both. The explanation should say which condition changed.

Back to contents

Quotes do not make the asterisk literal by themselves

The criteria strings in our formulas are enclosed in quotation marks. That tells the formula that the criterion is text in this syntax. It does not remove the wildcard role of an unescaped asterisk inside that text. Both "Draft*" and "Draft~*" are quoted, yet they produce different matching sets in our model.

A common misconception is that exact-looking quotation marks mean every character inside them must be taken literally for matching. The documented wildcard rules still apply. The learner needs the tilde for the literal special-character request, not simply quotation marks around the string.

Compare the roles clearly: quotation marks delimit the text criterion, while the tilde changes how a following special character is interpreted. They are not alternative spellings of the same instruction. Keeping those roles separate makes formula reading less mysterious.

This is a useful reminder about English explanations of notation. “Put it in quotes” may be an incomplete answer to a question about exact matching. A good instruction should identify which problem the change solves and which conditions still apply inside the quoted text.

Back to contents

Case sensitivity is another separate condition

The official guide notes that COUNTIF text criteria are not case-sensitive. Our main values use a consistent capital D so that case is not an extra complication. Do not assume the literal-asterisk criterion becomes case-sensitive merely because the star is escaped.

For a separate declared case, compare Draft* and DRAFT* as stored text values. Under COUNTIF’s documented case-insensitive text matching, the exact-literal-star criterion discussed here can match both forms. The escape addresses the special character, not every possible distinction in the string.

If a real task needs case-sensitive identity, establish the appropriate method from current authoritative instructions rather than claiming this formula supplies it. This article does not offer a substitute method or a universal text-equality standard. Its focus remains the literal-versus-wildcard decision.

This is another useful habit for students: name which condition a repair changes. “I escaped the asterisk” is precise. “I made the formula exact in every possible way” overstates the change. A narrow technical repair should not be reported as a broader guarantee than it provides.

Back to contents

Spaces and hidden differences are outside the clean example

Our declared range has no leading or trailing spaces. A real imported label may contain extra characters that are hard to notice. If a result differs from your prediction, inspect the actual supplied values through the approved process before concluding that the documented matching rule is wrong.

Do not silently remove characters from a shared source merely to obtain the expected number. A space may be an error, but the right repair depends on the source convention and authority to edit it. Preserve the original information as required and ask the responsible person when meaning is uncertain.

A useful report separates the observations: “The paper model predicts one exact label; the authorised workbook shows a different result; I have not yet confirmed whether its values contain additional characters.” That sentence avoids turning a discrepancy into a fabricated diagnosis.

The learning point is to check assumptions. A clean teaching model is helpful because its inputs are fully known. It is not a promise that every real file shares those inputs. English lets the learner name the assumption that needs checking instead of announcing that the whole spreadsheet is broken.

Back to contents

A label convention does not prove the underlying work was done

In our fictional organiser’s convention, Draft* is a specific status label. Counting that label tells us about the supplied cells. It does not independently verify the quality of the drafts, whether somebody actually reviewed them or whether a real task was completed.

This is why the report should say matching labels rather than verified finished work. Even a perfectly selected criterion cannot establish facts that the dataset does not supply. The formula’s job is to compare values under a rule; a separate review process establishes what the values reliably represent.

For a school project, the difference is practical. A group may use a status sheet to coordinate work, but the sheet should not replace reading the actual draft when the task requires review. The status label and the reviewed document are different sources of evidence.

English matters at the handover. “One supplied cell has the exact label” is accurate for our model. “Only one draft needs work” adds an interpretation that would need the organiser’s definition and reliable status information. A correct count becomes useful when the explanation keeps those boundaries visible.

Back to contents

Change the range without changing the criterion

Suppose a separate paper question uses only A2:A4 rather than A2:A7. The exact-literal-star criterion still matches A2 alone, so its count remains one. The broad Draft* pattern now matches A2, A3 and A4, giving three rather than five.

The criterion did not change, but the source range did. A learner who looks only at the criteria text may therefore miss why a count differs. Both the range and the criterion belong to the formula’s meaning.

Ask for a complete explanation: “The broad pattern matches three cells within A2:A4; two additional matches in A5 and A7 are outside this narrower question.” The sentence names what is included and what was deliberately excluded. It is more useful than saying the smaller formula somehow lost two results.

This is a good school-reading habit: attach a result to the material actually inspected. A claim about six supplied cells should not silently become a claim about three, or vice versa. Clear English preserves the boundaries that give a formula result its proper context.

Back to contents

Change one label and predict what should move

In a new counterfactual version of the six-cell range, replace A6’s Ready with Draft*. Keep the other five values unchanged. The exact-literal-star criterion now matches A2 and A6, so its count becomes two. The broad prefix pattern now matches all six cells.

The one-character wildcard criterion also changes because the new A6 value has exactly one character after Draft. Its matching set becomes A2, A3, A5 and A6. This is a separate teaching scenario, not an update actually made to a workbook.

Predicting the sets before calculating the totals helps the learner connect a changed input with the affected rules. If a criterion should include the changed value, its count changes; if not, it may remain stable. The result depends on the specified relationship, not on the mere fact that somebody edited a cell.

This is useful practice for explaining a test. State the original input, the single changed value and the predicted consequences. Do not change several things and then claim that one of them caused every difference. The small example gives students a clear way to talk about a controlled comparison.

Back to contents

Parent advice: use cards before a workbook

Write the six supplied labels on paper cards. Ask your child to select the exact Draft* card first, without any formula. Then ask for all cards beginning with Draft. The difference between one selected card and five selected cards makes the two questions visible.

Only after that comparison, introduce the criteria notation. The tilde now has a purpose the child can explain: it tells the pattern to treat the star as a literal character. The formula is connected to an understood question rather than presented as a trick to memorise.

If the child selects A7 for the exact request, compare its two stars with A2’s one. If they reject A4 from the broad pattern, discuss the empty suffix allowed by that wildcard. Different mistakes need different explanations; more rapid formula copying will not automatically address either one.

Keep practice harmless and authorised. No real class records, private work or shared status sheets are needed. A short paper exercise can teach the essential distinction, and a later permitted software practice can follow the current instructions for the actual environment.

Back to contents

Student practice: write the set, criterion and report

Use three stages. First identify the intended matching set from the supplied labels. Second choose the criterion that expresses that boundary. Third write a sentence reporting the count with its range and meaning. Each stage can reveal a different error.

For the literal-star question, the set is A2, the criterion is Draft~* and the report is: “One of the six supplied cells contains the exact one-star label under the stated matching conditions.” The explanation is longer than the formula because it makes the task boundary explicit.

For the prefix question, the set has five cells and the criterion is Draft*. The report should name labels beginning with Draft, not the exact one-star label. A learner who can produce both reports has understood why two nearly similar formulas can answer different questions accurately.

Afterwards, choose an ordinary school task with a different symbol or label convention. Ask what exact means in that task and which source defines the symbol’s role. Transfer should be checked through a new explanation, not assumed merely because the original formula was remembered.

Back to contents

A compact checking route

• State the intended request in ordinary language.
• Identify the exact range being inspected.
• Decide which characters are literal and which are pattern instructions.
• Predict the matching cells before trusting the count.
• Report the result without claiming unverified facts about the underlying work.

Use this route when a criterion is confusing. It is not a requirement to narrate every routine formula at length. Once the meaning is shared, a concise note can be enough. The important point is that the learner can expand the explanation when another person needs to inspect it.

Back to contents

A formula-reading clinic with four different errors

The formula is valid but the question was broader than intended

The unescaped Draft* pattern fits a starts-with request in our model. It fails the organiser’s exact one-star request. Preserve the correct understanding of the pattern while changing the criterion to match the intended question. The problem is not random arithmetic.

The count is right but the matching cell is wrong

The literal-star and literal-question-mark criteria both return one. They refer to different supplied labels. Ask which cell belongs to the result. Equal totals are not enough when the task concerns a particular status spelling.

The learner thinks quotation marks disable wildcards

Compare the two quoted criteria directly. Both are text strings in the formula syntax, but the unescaped star still has its documented pattern role. Explain what quotation marks do and what the tilde does separately. Do not replace one vague instruction with another.

The workbook result differs from the clean model

Inspect the actual range, values and assumptions through the authorised route. Do not invent a cause or modify source records merely to match the teaching count. A precise discrepancy report can be a successful next step even before the real-file question is resolved.

Back to contents

Connect the skill with education and later work

The practical value of English is translating a human request into a checkable condition. This appears in school spreadsheets, project records and later data-related tasks. The user’s phrase exact label needs a more specific interpretation than a visually similar formula can supply by itself.

This article does not teach an entire computing syllabus or make claims about examination requirements. The existing guide to learning Excel through formulas and practice provides a broader route. Here the focus is one literal-versus-pattern decision that a learner can explain independently.

A student who enjoys this kind of precise question may be interested in computing, information work or analysis. Explore real courses and roles through their current official information. A successful criterion exercise does not guarantee admission, employment or earnings; it offers a small way to discover an interest and practise clear reasoning.

Other languages can support the same thought process. A family can discuss the intended set in a familiar language and then connect it with the English-language interface and instructions. English adds a useful shared channel without making other languages less valuable for careful explanation.

Back to contents

Frequently asked questions

Does putting the criterion in quotes make the asterisk literal?

No. In the documented COUNTIF syntax, quoted text criteria can still contain wildcards. In our model, Draft* matches five supplied values, while Draft~* matches the exact one-star value. The tilde addresses the special character’s matching role.

Why does plain Draft match the broad asterisk pattern?

The wildcard can match an empty sequence after the fixed prefix. Our A4 value therefore fits Draft* even though it has no additional character. A question-mark wildcard has a different one-character condition and does not match A4.

Do two one-cell results mean the criteria are equivalent?

No. The exact-star criterion matches A2, and the exact-question-mark criterion matches A5. Their totals coincide in this supplied range, but their matching sets and meanings differ. Inspect the labels, not only the number.

Does escaping a star make COUNTIF case-sensitive?

No. Microsoft’s guide states that COUNTIF text criteria are not case-sensitive. The escape changes the star’s role. It does not supply every possible kind of exact-text comparison, so do not describe it as a universal identity check.

Are these observed Excel results?

No. They are independently checked predictions from explicit original strings and the documented wildcard rules. No actual workbook or account is changed. Use an authorised practice copy and the applicable instructions if you later test the example in software.

Does a matching status label verify the work behind it?

No. The count concerns supplied values. The organiser’s definition and a reliable review process are separate evidence about what those values represent. Report matching labels without turning the formula into a guarantee of completed or high-quality work.

Back to contents

Next reading and the question to keep

For a different text-matching task, read about Word whole-word searches and the meanings they do not decide. That guide retains its search-and-editing purpose; this one concerns COUNTIF’s criteria notation. For collaborative use of information, continue with school projects, research and group work.

The English Learning Hub, How English Works and Why English Matters connect the exercise with broader language learning. Choose the route that answers your next question rather than treating the links as a new checklist to finish.

Keep one question close: “Do I mean this character itself, or an instruction to match something else?” Answering it carefully can turn a surprising spreadsheet count into a clear, explainable decision. That is English doing practical work alongside a formula.

Back to contents