Function Problems

How to Count TRUE Values Correctly with COUNTIF in Excel

Aamir Naveed Akram Oct 9, 2026 871 views

Question details

The user needs to accurately count occurrences of TRUE values generated by VLOOKUP using the COUNTIF function, but encounters issues where the formula returns an incorrect count or zero.

How to Count TRUE Values Correctly with COUNTIF in Excel 365

Product: Microsoft Excel 365
Device & OS: not provided
Scenario: Counting TRUE or FALSE results from a VLOOKUP formula in a dataset.
Observed behavior: COUNTIF fails to count the TRUE/FALSE values properly because the target cells may contain logical values, text strings, or invisible characters like hidden spaces.

Before you start

Before applying a formula, check whether your TRUE/FALSE values are formatted as logical values (which automatically center in the cell) or text strings (which align to the left by default), as this determines the correct COUNTIF syntax.

Solution 1Recommended

Count Logical TRUE Values with COUNTIF

Use this method if your VLOOKUP results are standard Excel logical values, which are not treated as text strings.

TRUE and FALSE are recognized as special logical values in Excel. When counting these, you should never enclose the criteria in quotation marks.

Select the output cell

Click on the empty cell where you want the final count to be displayed.

Enter the COUNTIF formula

Type =COUNTIF(O2:O22, TRUE) in the formula bar. Replace 'O2:O22' with your actual data range.

Execute the function

Press Enter to execute the formula and get the count of your logical TRUE values.

No Quotes Needed: By omitting the quotation marks around TRUE, Excel correctly identifies that it needs to count the logical Boolean value rather than a text word.

Solution 2

Count TRUE Values Formatted as Text Strings

If your TRUE values were entered manually, imported as text, or evaluated as text strings by another formula, COUNTIF requires quotation marks around the criteria.

Solution 3

Clean and Count TRUE Values with Hidden Spaces

When cells contain invisible characters or trailing spaces, standard COUNTIF fails. Use a combination of SUM and TRIM to resolve this.

Advanced Data Processing

Easily Count and Analyze Data with WPS Spreadsheet

You can seamlessly count logical values, text, and clean up hidden spaces using COUNTIF and TRIM functions in WPS Spreadsheet, a fully compatible and lightweight alternative to Excel.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your VLOOKUP results or raw data.
2. Apply the COUNTIF function: Select a blank cell and enter =COUNTIF(Range, TRUE) for logical values or =COUNTIF(Range, "TRUE") for text strings.
3. Get instant results: Press Enter to instantly count your TRUE values with flawless accuracy, using the same familiar workflow as Excel.

100% compatible with Microsoft Excel formulas, functions, and formattingBuilt-in data cleaning capabilities to handle trailing spaces instantlyLightweight architecture ensures smooth calculation of complex array formulasFree to use for everyday data analysis and professional reporting

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIF return 0 when I try to count TRUE values?

This usually happens because of a data type mismatch. Excel distinguishes between the logical value TRUE and the text string "TRUE". If your formula uses text syntax but the cells contain logical values (or vice versa), the resulting count will be 0.

How can I visually check if my TRUE values are logical or text?

By default, if the column is wide enough, logical TRUE/FALSE values are automatically centered within the cell, while text strings are aligned to the left side. You can also use functions like =ISTEXT(A1) or =ISLOGICAL(A1) to verify the data type.

Can I count both TRUE and FALSE values at the same time?

Yes, you can combine counts by adding two formulas together, such as =COUNTIF(O2:O22, TRUE) + COUNTIF(O2:O22, FALSE). Alternatively, using a Pivot Table is a highly efficient way to summarize the count of all logical values in your dataset simultaneously.

Excel & Data Problems