Arin Islam
The COUNTIF function is one of the most useful functions in Excel. It is used to count values if the function is true for the given criteria. But sometimes this function may not work when you try to count the string “True”. Here, we will give you 3 possible solutions when the Excel COUNTIF function is not working for the string “True”.
How to Fix When Excel COUNTIF Function Is Not Working for String “True”: 3 Possible Solutions
Here, we have a dataset where we have the Name of some students and the status of whether they have submitted their assignments or not. Now, we will use this dataset to give some solutions to your problem regarding using the COUNTIF function to count True.
1. Add Double Quotation Mark While Counting True with COUNTIF Not Working in Excel
When you are counting any string value in Excel using the COUNTIF function make sure you have added a double quotation mark (" ").
- Suppose, you have used a single quotation mark
(' ')instead of a double quotation mark(" ").
- Then, it may show an error like the image given below.
Solution:
To avoid this kind of error, follow the steps given below.
- Firstly, select Cell C13 and insert the following formula.
=COUNTIF(C5:C11,"TRUE")- Finally, press Enter to get the count value.
Read More: [Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value
2. Input Correct Range Reference If COUNTIF Function Stops Working for True
Next, check if you have inserted the wrong cell range as reference in the COUNTIF function. This may return a wrong value or error.
Here, in the image given below, you can see that we have inserted cell range B5:B11 instead of cell range C5:C11 in the COUNTIF function. That’s why the function has returned 0 as a result.
Solution:
To solve this issue, insert the following formula in Cell C13.
=COUNTIF(C5:C11,"TRUE")
Read More: [Fixed] COUNTIF Function with Wildcard Not Working in Excel
3. Insert Wildcards When COUNTIF Function Is Not Working for String “True”
Sometimes, your dataset may contain blanks in the cells. That’s why the COUNTIF function may not work properly.
Solution:
For datasets that contain blanks, you can use different Wildcards. Here, we will use asterisk (*) as a wildcard to solve this issue.
- Firstly, select Cell C13 and insert the following formula.
=COUNTIF(C5:C11,"*True*")- After that, press Enter.
Excel COUNTIF Function Not Working with Text
You can count any text value using the COUNTIF function. However, sometimes raw dataset may have blanks before or after the text. In those cases, this function does not work properly.
Here, we have a dataset containing some students’ assignment submission statuses. “Y” represents an assignment that has been submitted and “N” represents not submitted. Now, if we try to count “Y” using the COUNTIF function, it returns 0.
Solution:
In such cases, try to use asterisk (*) as a wildcard to solve the error created by blank spaces.
Here, we will insert the following formula in Cell C13 which will return the count value perfectly.
=COUNTIF(C5:C11,"*Y*")
Read More: Excel COUNTIF to Count Cell That Contains Text from Another Cell
Download Practice Workbook
You can download the workbook to practice yourself.
Conclusion
So, in this article, we have shown you 3 ways to solve the issues when Excel COUNTIF function is not working for the string “True”. I hope you found this article interesting and helpful. However, if something seems difficult to understand, please leave a comment. Additionally, please let us know if there are any more alternatives that we may have missed.
Related Articles
- How to Use COUNTIF for Non Contiguous Range in Excel
- How to Use COUNTIF Function to Count Text from List in Excel
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- How to Compare Two Columns Using COUNTIF Function
- How to Use Excel COUNTIF Between Time Range
- How to Use COUNTIF to Count Date Less Than Today in Excel
- Excel COUNTIF to Count Cells Greater Than 1
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use COUNTIF Function In Excel to Count Bold Cells
- How to Use COUNTIF Function to Calculate Percentage in Excel
- How to Use COUNTIF Function in Excel Greater Than Percentage
- How to Use COUNTIF Function with Array Criteria in Excel
- How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
- How to Use the Combination of COUNTIF and SUMIF in Excel
- Difference Between SUMIF and COUNTIF Functions in Excel
- How to Use IF and COUNTIF Functions Together in Excel
- How to Use Nested COUNTIF Function in Excel
- How to Use COUNTIF and COUNTA Functions Together in Excel
- How to Calculate Frequency Using COUNTIF Function in Excel
- Excel COUNTIF Function with Conditional Formatting
