To count every p in the text in cell A2, use =LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"p","")). To count cells in a range that contain p at least once, use =COUNTIF(A2:A20,"*p*"). These answer different questions: the first counts letter occurrences, while the second counts matching cells.
What “count p” can mean
People asking how to count p in Excel may mean one of several things. They might want the number of times the letter appears in a single entry, the total number of appearances across a column, or the number of cells whose contents include that letter. A fourth possibility is counting cells that contain only p, rather than cells that contain p among other text.
Choose the calculation based on what should count as one result:
- Each letter occurrence: A value such as pepper contributes three p characters.
- Each cell containing the letter: Pepper contributes one matching cell, even though it has three p characters.
- Exact cell match: A cell containing just p qualifies, but pepper does not.
Excel’s COUNTIF function is useful for counting cells that meet a condition. It does not count how many times a character occurs inside each matching cell. For that, compare the original text length with its length after removing the target character.
Count p occurrences in one cell
Assume the text to check is in A2. This formula counts lowercase and uppercase p together:
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"p",""))
LOWER converts the text to lowercase, SUBSTITUTE removes every p, and LEN measures the text before and after removal. Subtracting the second length from the first gives the number of characters removed. For example, if A2 contains Poppy, LOWER turns it into poppy; removing p leaves oy. The lengths are five and two, so the result is 3.
If capitalization should matter and only lowercase p counts, use this version for text in A2:
=LEN(A2)-LEN(SUBSTITUTE(A2,"p",""))
With Poppy in A2, this case-sensitive calculation returns 2 because it counts the two lowercase p characters and ignores the initial uppercase P. SUBSTITUTE is case-sensitive, so removing lowercase p from the original text does not remove uppercase P.
These formulas count character occurrences, not words. A p within a longer word counts just as much as a p in a standalone entry. They also return zero for an empty cell or text with no matching p.
Count cells that contain p
To count cells containing p anywhere in their contents, assume the data is in A2:A20. Use:
=COUNTIF(A2:A20,"*p*")
The asterisks are wildcards: each can represent any number of characters, including none. Together with p, they match text before or after the letter. COUNTIF is not case-sensitive, so this condition matches both p and P. If four cells contain Paper, apple, pear, and P, the result is 4; a cell containing kiwi is not included. Each matching cell counts once regardless of how many p characters it contains.
To count cells whose entire content is p, assume the same A2:A20 range and use:
=COUNTIF(A2:A20,"p")
This counts cells equal to p, with COUNTIF’s usual case-insensitive comparison. It does not match pepper or a cell containing a space before or after p. The distinction between a wildcard condition and an exact condition is often the key to getting the expected answer.
Count every p across a range
If you need the total number of p characters across several cells, rather than the number of cells that contain p, use a length-difference calculation over the range. With text in A2:A20, this formula counts uppercase and lowercase occurrences together:
=SUMPRODUCT(LEN(A2:A20)-LEN(SUBSTITUTE(LOWER(A2:A20),"p","")))
For each cell, the calculation measures the characters removed when p is substituted out; SUMPRODUCT adds those differences. For example, if A2 contains Poppy, A3 contains apple, and the remaining cells in A2:A20 are blank, the result is 5: Poppy has three p characters and apple has two. This method is appropriate when repeated occurrences should contribute repeatedly.
Keep the range limited to the rows containing data. Applying text transformations and length calculations to an entire column can make a workbook slower, especially when many such formulas are present. If the list grows regularly, choose a sufficiently large range or use an Excel Table so the data range can expand with new rows.
Make the count case-sensitive or case-insensitive
Decide whether P and p are equivalent before choosing a formula. The versions using LOWER count both forms by converting the text to lowercase first. The version without LOWER uses SUBSTITUTE directly and counts only the exact lowercase character specified. COUNTIF, by contrast, does not distinguish letter case in its criteria.
For a case-sensitive total across cells, assume the text is in A2:A20 and use:
=SUMPRODUCT(LEN(A2:A20)-LEN(SUBSTITUTE(A2:A20,"p","")))
This counts lowercase p but not uppercase P. To count uppercase P instead, replace the p in the formula’s search argument with P. If a report needs separate totals for the two cases, calculate them separately rather than converting the text with LOWER.
Case sensitivity is separate from exact matching. A case-insensitive exact-cell condition can still require the whole cell to equal p, while a case-sensitive character formula can count p wherever it occurs in a phrase. Keep both decisions in mind: whether to count occurrences or cells, and whether capitalization should matter.
Build a reusable worksheet setup
For a simple list, put the words or descriptions in column A and put the occurrence formula beside the first value in B2. With the text to evaluate in A2, enter:
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"p",""))
Copy the formula down column B using the fill handle at the lower-right corner of B2. Excel adjusts A2 to A3, A4, and so on as the formula is copied. The resulting column gives a per-cell count, which can then be added if you need an overall total.
For a one-cell summary of the entire list, the SUMPRODUCT formula is more direct. If you want to audit the result, keeping per-row counts can help locate which entries contribute to the total. You can also sort or filter the source list without changing the counting logic, provided the formulas continue to refer to the intended cells.
When the source values are imported or frequently updated, check that they are actually stored as text or ordinary cell values. The formulas work on cell contents, not on how a value merely looks on screen. For example, a formula result that displays p is counted based on its evaluated text, while a cell containing a number formatted in a way that resembles a letter is not the same as text p.
Common issues and checks
- Counting cells instead of letters: COUNTIF with asterisks returns one for a cell with one p and one for a cell with several p characters. Use the length-difference formula to total occurrences.
- Unexpectedly ignoring uppercase P: A formula using SUBSTITUTE without LOWER is case-sensitive. Use LOWER for a combined total or change the search character to P.
- Counting only exact matches by mistake: The criterion p matches a cell containing only p. The criterion *p* matches p anywhere in the cell.
- Leading or trailing spaces: Spaces do not stop the occurrence formula from finding p, but they do prevent a cell from being an exact match for p. Check the intended matching rule before changing the data.
- Formula displayed instead of a result: Confirm that the cell is not formatted as Text, then re-enter the formula. Also check that it begins with an equals sign.
- Wrong argument separators: The formulas shown use commas, as in standard Microsoft 365 Excel for Windows settings. Some installations configured for different regional settings use semicolons as formula separators; follow the separator Excel expects on that computer.
COUNTIF supports wildcard criteria, so a literal asterisk or question mark in the condition has special meaning unless it is escaped with a tilde. That detail does not affect the simple p criteria shown here, but it matters when adapting the method to search for wildcard characters themselves. For ordinary alphabetic p, no escaping is needed.
Choosing the right formula
Use the shortest method that matches the question. If you are checking one text value for every occurrence, use LEN and SUBSTITUTE. If you need the number of records containing the letter, use COUNTIF with wildcards. If you need records equal to the single-character value, use COUNTIF without wildcards. If the letter count spans multiple rows, use SUMPRODUCT with the length difference.
It can help to test a formula against a few known examples before applying it to a large list. Include a value with no p, one with a single p, one with repeated p characters, and one with uppercase P. These cases reveal whether the formula is counting occurrences, counting matching cells, and handling capitalization as intended. A known-answer check also helps identify spaces or unexpected source data.
Frequently Asked Questions
How do I count how many times p appears in a cell?
For text in A2, use =LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"p","")). It counts both p and P. Remove LOWER if you want to count lowercase p only.
How do I count cells that contain the letter p?
If the cells are A2:A20, use =COUNTIF(A2:A20,"*p*"). This counts each matching cell once, even if p appears more than once, and it matches either capitalization.
Does COUNTIF count repeated p characters?
No. COUNTIF counts cells that satisfy a condition, not the number of times the character occurs inside each cell. Use the LEN and SUBSTITUTE method for occurrences, or SUMPRODUCT to total occurrences across a range.
How can I count uppercase P and lowercase p separately?
For text in A2, use =LEN(A2)-LEN(SUBSTITUTE(A2,"P","")) for uppercase P and =LEN(A2)-LEN(SUBSTITUTE(A2,"p","")) for lowercase p. These formulas are case-sensitive because SUBSTITUTE distinguishes the two forms.
Can I count p across a whole column?
You can use a range-based SUMPRODUCT formula, but a bounded range such as A2:A1000 is usually more efficient than processing a full column. Expand the range when needed, or use a table-based setup if the list changes frequently.
Related
- Excel Conditional Formatting for Duplicates: How It Works
- Compare Excel Workbooks: Methods, Steps, and Differences
- Company Cash Flow Template Excel: Build and Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Growth Analysis: Formulas, Trends, and ExamplesExcel growth analysis measures how a value changes over time. For two consecutive values in B2 and B3,…
- Java 8 String Functions: Common Methods and ExamplesIn Java 8, “string functions” usually means methods on the String class, such as length(), substring(), contains(), and…
- XLOOKUP Example With IFERROR: Excel Formula and TipsAn XLOOKUP formula wrapped in IFERROR can show a useful message instead of an Excel error: with the…
- Balance of Payments Equation: Formula and Key TermsThe balance of payments equation records a country’s transactions with the rest of the world: current account +…
- Excel Formula to Concatenate Names: Examples and TipsTo concatenate first and last names in Excel, use =A2&" "&B2 when the first name is in A2…
- Count Days in Excel: Formulas for Dates and WorkdaysTo count calendar days between two dates in Excel, put the start date in A2 and the end…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.