Count P in Excel: Formulas to Count Letters and Matches

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.