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”.
Download Practice Workbook
You can download the workbook to practice yourself.
3 Possible Solutions When Excel COUNTIF Function Is Not Working for String “True”
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: [Fixed] Excel COUNT Function Not Working
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!] Formulas Are Not Calculating Automatically in Excel
Similar Readings
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)
- [Fixed!] Merge Cells Button Is Greyed Out in Excel
- [Fixed!] Excel Scroll Bar Too Long (5 Solutions)
- Excel Data Validation Greyed Out (4 Reasons with Solutions)
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.
Read More: [Fixed!] Excel Filter Stops at Blank Row (4 Possible Solutions)
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: [Fixed!] IF Function Is Not Working in Excel (4 Quick Solutions)
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. And visit ExcelDemy for many more articles like this. Thank you!
Related Articles
- [Fixed!] Excel Hyperlink Keeps Coming Back (5 Quick Solutions)
- [Solved:] Excel Not Filtering Entire Column (3 Quick Fixes)
- [Fixed!] Unshare Workbook Greyed Out in Excel
- [Fixed] Excel Date Filter Not Working
- [Fixed!] Excel Scrolling Too Many Rows (2 Easy Solutions)
- [Solved]: Macro Settings Greyed out in Excel