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
- Click an empty cell where you want the result to appear.
- Type
=COUNTIF(. - Select the column range (e.g.,
A:Afor the whole column, orA2:A100for a specific range). - Type a comma, then
"X"(with quotes). - 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
- Excel Conditional Formatting for Duplicates: How-To
- Excel Conditional Formatting for Past Dates: How to Set It Up
- Excel Conditional Formatting for Past Dates
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Keep Track of Vacation Days in ExcelCreate a simple Excel vacation tracker with start and end dates, leave type, approval status, and a list…
- Unmerge Cells in Excel: Keyboard Shortcuts and StepsTo unmerge cells in Excel for Microsoft 365 on Windows, select a merged cell or merged range, then…
- How to Compare Differences in Excel: Cells and SheetsTo compare values in the same row, enter a comparison formula such as =IF(A2=B2,"Match","Different") in a spare column…
- Excel Import and Align with Existing TimesYes, Excel can import data and align it with existing times, but it depends on what you mean…
- Compare Two Excel Sheets and Find DifferencesTo compare two Excel sheets cell by cell, put a formula on a third sheet such as =IF(Sheet1!A1=Sheet2!A1,"Same","Different"),…
- How to Delete a Page in Word UK: A Practical GuideTo delete a page in Word, remove the text, paragraph marks or break creating it; Word does not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.