Countif formula not working if used the same range as the data it is searching for

[Dan23]

Hi,

This is my formula: =COUNTIF(range,"=*Y*"); also tried =COUNTIF(range,"*Y*"); and =COUNTIF(range,"Y").
Goal: trying to count the number of "Y"s within the cell range
Problem: Formula works only if I enter it outside of the range within the formula i.e if I want to find all Ys within the whole C coloum (C:C) and I enter this formula any other column but C, the formula displays the correct result. However if I enter the forumula within the C column the formula displays 0

 

[James006]

Hi,

Just be specific with your range B2:B1000

Hope this will help

 

[Dan23]

Thanks James, but still same problem

 

[James006]

Thanks James, but still same problem

Rather strange ...

Have you filtered your column B to find out the potential problem ?

 

[Dan23]

Sorry I am not sure how to filter to find the problem, however the same error occurs for all of my columns.

 

[James006]

Is there a chance you could upload your file to dropbox or google drive ?

 

[Dan23]

Yes, no problem, thankyou

 

[vds1]

This is a circular reference error where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells.

In column B, it should be in B1:

=COUNTIF(B2:B1000,"y")

Also make sure the calculation options should be set to automatic under formulas

 

[Dan23]

VDS I think you are right, I received a circular reference error message when restarting the workbook. Will try your suggestions tomorrow when I have time and communicate if problem is solved. Here is a link to it in case you wanted to check it out:

Y&N.xlsx

drive.google.com

 

[Dan23]

Problem solved, thanks to VDS1's suggestion, instead of b:b used B2:B1000. Would prefer to use B:B as it is more efficient to click on the column than type in myself, however document will not be used frequently so is a minor inconvienience. Thanks for VDS and James for your assistance.