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

Dan23

New Member
Joined
Dec 4, 2019
Messages
6
Office Version
2013
Platform
Windows
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
 

Attachments

Dan23

New Member
Joined
Dec 4, 2019
Messages
6
Office Version
2013
Platform
Windows
Sorry I am not sure how to filter to find the problem, however the same error occurs for all of my columns.
 

Dan23

New Member
Joined
Dec 4, 2019
Messages
6
Office Version
2013
Platform
Windows
Yes, no problem, thankyou
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,186
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:

Code:
=COUNTIF(B2:B1000,"y")

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

Dan23

New Member
Joined
Dec 4, 2019
Messages
6
Office Version
2013
Platform
Windows
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:

 

Dan23

New Member
Joined
Dec 4, 2019
Messages
6
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,077,938
Messages
5,337,296
Members
399,138
Latest member
eurogator

Some videos you may like

This Week's Hot Topics

Top