Often times I have what appear to be exact cell values say, say "blue" and I drive the sum or count formula if cell criteria is "blue" For some reason even though both cells are populated with "blue" it counts one but not the other? This has been a common occurrence for me in excel with these formulas. Please help.
Welcome to the forum.
It might be that you have leading or trailing spaces with some values, or even non-printing characters like the non-breaking space (code 160), but it is hard to say exactly without seeing your data. The yellow banner a the top of the screen gives details of how to attach a sample Excel workbook to one of your posts.
Hope this helps.
Pete
an option might be to point this formula to both cells, =len(A2) assuming that blue is in A2 then point the same formula to the other one that isn't being counted.
if they return different numbers there might be (as Pete noted above) a space you aren't seeing.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Others have pointed out likely reasons your data may not be what you believe it is. On a different tangent, if cell X99 displays blue, try =X99="blue". If that formula returns FALSE, then X99 includes other characters whether you see them or not. You could try =CODE(SUBSTITUTE(X99,"blue","",1)) to see the character code of the 1st character in X99 after removing "blue". Or just ="["&X99&"]" to see if there are additional spaces.
Thank you all. This resolved the issue there were hidden characters.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks