Excel: How to Count X’s in a Column

How-to basics · Updated November 22, 2026

The fastest way to count X’s in a column is with the COUNTIF function. For example, if your data is in column A (A1:A100), enter =COUNTIF(A1:A100,"X") in any empty cell to get the total number of cells that contain exactly “X”. This works in Microsoft Excel 365, Excel 2021, and older versions, and the same formula works in Google Sheets.

Why COUNTIF Is the Standard Tool

COUNTIF is Excel’s dedicated function for counting cells that meet a single condition. It takes two arguments: the range to look at and the criteria to match. The criteria can be a number, text, or a logical expression. When you want to count X’s, the criteria is simply the text “X” (with quotes).

Important nuance: COUNTIF is case-insensitive. It will count both “X” and “x”. If you need to count only uppercase X or only lowercase x, you’ll need a different approach—see the case-sensitive section below.

Step-by-Step: Counting X’s in a Column

  1. Click an empty cell where you want the result to appear.
  2. Type =COUNTIF(.
  3. Select the column range (e.g., A:A for the whole column, or A2:A100 for a specific range).
  4. Type a comma, then "X" (with quotes).
  5. Close the parenthesis and press Enter. Example: =COUNTIF(A:A,"X").

If your column has headers, start the range at the first data row (e.g., A2:A100) to avoid counting a header that says “X”.

Counting X’s with Additional Criteria

Sometimes you need to count X’s only when another column meets a condition. For example, count X’s in column A where column B equals “Yes”. Use COUNTIFS (plural), which accepts multiple range/criteria pairs.

Example layout: A2:A20 contains X or blank, B2:B20 contains Yes/No. Formula: =COUNTIFS(A2:A20,"X",B2:B20,"Yes"). This returns the number of rows where both conditions are true.

Case-Sensitive Counting (Uppercase vs. Lowercase)

COUNTIF treats “X” and “x” as the same. To count only uppercase X, use SUMPRODUCT with EXACT:

Formula: =SUMPRODUCT(--EXACT(A2:A20,"X")). This returns the count of cells that exactly match “X” including case. For lowercase x, change the criteria to “x”.

How it works: EXACT compares each cell to “X” and returns TRUE/FALSE. The double negative (–) converts TRUE to 1 and FALSE to 0, and SUMPRODUCT sums those 1s.

Counting X’s as Part of a Cell’s Text

If you want to count cells that contain the letter X anywhere (e.g., “AXB”, “X-ray”), use wildcards. The formula =COUNTIF(A2:A20,"*X*") counts cells that contain an X anywhere. The asterisk (*) is a wildcard that matches any sequence of characters.

Note: This still ignores case. Also, if you want to count cells that start with X, use "X*"; end with X, use "*X".

Common Problems and Fixes

  • Counting blank cells accidentally: If your range includes empty cells, they are ignored because they don’t match “X”. But if you use "*X*", empty cells are still ignored.
  • Counting cells with extra spaces: If a cell contains “X ” (trailing space), COUNTIF won’t count it. Use =COUNTIF(A2:A20,"X*") to count cells that start with X, or clean data with TRIM.
  • Formula returns 0 even though X’s exist: Check for non-breaking spaces or other invisible characters. Use =TRIM(A2) to see if the cell is really “X”.
  • Counting X’s in a filtered list: COUNTIF counts all cells, not just visible ones. To count only visible X’s after filtering, use SUBTOTAL or AGGREGATE with function 3 (COUNTA) and a helper column, or use a more advanced formula with SUMPRODUCT and SUBTOTAL.

Alternatives: PivotTables and Advanced Filter

If you need a dynamic count that updates as you add data, a PivotTable is a good alternative. Create a PivotTable from your column, drag the column to both Rows and Values, and it will show the count of each unique value, including X. This is useful for a quick summary without writing formulas.

Another option is to use the status bar: select the column (or a range) and look at the bottom-right corner of Excel. If you right-click the status bar, you can enable “Count” to see the number of non-empty cells, but that doesn’t count specific text. For a quick count of X’s, you can use Find & Select: Press Ctrl+F, type X, click “Find All”, and the status bar shows “X cells found”. This is a manual method, not a formula.

Frequently Asked Questions

How do I count X’s across multiple columns?

Use COUNTIF for each column and add them, e.g., =COUNTIF(A2:A20,"X")+COUNTIF(B2:B20,"X"). Or use SUMPRODUCT to count across a 2D range: =SUMPRODUCT(--(A2:C20="X")) counts all X’s in the range A2:C20.

Can I count X’s in a column that has formulas returning “X”?

Yes, COUNTIF counts the displayed result of a formula, not the formula itself. If a cell contains =IF(...) that returns “X”, COUNTIF will count it as long as the result is exactly “X”.

How do I count X’s in a column in Google Sheets?

Use the same COUNTIF formula: =COUNTIF(A:A,"X"). The syntax is identical. For case-sensitive counting, use =SUMPRODUCT(--EXACT(A2:A20,"X")) as well.

Why does my COUNTIF formula count “X” but not “x”?

Actually, COUNTIF is case-insensitive, so it counts both. If you see it not counting lowercase, check for spaces or different characters. If you specifically need case-sensitive, use the SUMPRODUCT/EXACT method.

Is there a way to count X’s without a formula?

Yes, use Find & Select: Press Ctrl+F, type X, click “Find All”, and the status bar shows the count. This is a one-time manual count, not a live formula.

How do I count X’s in every other row?

Use SUMPRODUCT with MOD: =SUMPRODUCT((A2:A20="X")*(MOD(ROW(A2:A20)-ROW(A2),2)=0)) for even rows (assuming A2 is the first row). Adjust the MOD divisor to 2 for every other row.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.