COUNTIFS not capturing data

[datadummy]

I have the following formula COUNTIFS('Foley Bundle'!$A:$A,">2/28/2017",'Foley Bundle'!$A:$A,"<4/1/2017",'Foley Bundle'!$B:$B,"CCU",'Foley Bundle'!C:C,"Y") that is not capturing the data that it should. This formula has been in place for about 6 months and started missing data in January and now won't capture the data at all. Also I have other cells with the exact formula except where it reads "CCU" there are other names in the quotes and those are working properly so I am totally stumped on why this one isn't working.

 

[63falcondude]

Since you are saying that the formula works with criteria other than "CCU", let's (for now) assume that is the problem and go from there.

Does =COUNTIF('Foley Bundle'!$B:$B,"CCU") return the correct number of "CCU" rows in column B?

If not, it is possible that there are trailing or leading spaces or other characters besides just "CCU" in the cell? For example, maybe the cells show " CCU" or "CCU ".

To test if this is the case, try adding wildcards around CCU in your formula and see if it picks them up.

"*CCU*"

 

[datadummy]

Since you are saying that the formula works with criteria other than "CCU", let's (for now) assume that is the problem and go from there.

Does =COUNTIF('Foley Bundle'!$B:$B,"CCU") return the correct number of "CCU" rows in column B?

If not, it is possible that there are trailing or leading spaces or other characters besides just "CCU" in the cell? For example, maybe the cells show " CCU" or "CCU ".

To test if this is the case, try adding wildcards around CCU in your formula and see if it picks them up.

"*CCU*"

BOOM! That did the trick, thanks for the help!! Question, any idea how this may be occurring when the cell where CCU is being entered is coming from a drop down, so shouldn't all the entries be uniform? My only thought is we have a culprit who may be free texting CCU.

 

[Jonmo1]

How is that drop down populated, is it data validatoin?
Can you post the contents of the source box?

 

[63falcondude]

BOOM! That did the trick, thanks for the help!! Question, any idea how this may be occurring when the cell where CCU is being entered is coming from a drop down, so shouldn't all the entries be uniform? My only thought is we have a culprit who may be free texting CCU.

Great! Now that we found the issue, let's work on correcting it.

First thing that I would check is the drop down value itself. Make sure that it does not have any spaces around "CCU".

 

[datadummy]

How is that drop down populated, is it data validatoin?
Can you post the contents of the source box?

Yes the drop down is populated via data validation and after reviewing it I believe I may have found the problem, an added space after the unit name.

 

[datadummy]

I think that was the issue after looking at my data validation list it appeared there was a space after CCU and not the other unit names. Thanks for walking me through that, totally saved a lot of rework!!