COUNTIF not counting all values

[cleveland_sara]

Hi all. I am having a problem with a COUNTIF function selectively counting. The formula is correct and I am looking at options of "y", "n", or "n/a" for a range of cells (not shown).

At the bottom of the range I have a total line for each above plus overall total. There should be 32 as the toal for each of the columns, but for some reason, on a few rows the COUNTIF will only count if it is an "y" or "n/a", thus the "n" column does not have the correct number of entries. The formula I am using for each range is =COUNTIF(AG217:AG513, "y") (or "n" or "n/a")

yes 31 30 31 24 24 24
no 1 2 1 6 5 4
n/a 0 0 0 0 0 0
total 32 32 32 30 29 28

Why won't it count these entries in select cells? I have gone through and replaced the text and it still won't work; however, if replacing with "y" or "n/a" it does count. Is it a problem/glitch within excel itself? Please let me know how to resolve this. Thanks!

 

[Kamikazenaz]

hello sara,

in an adjacent range type =TRIM(B1) or wherever your data starts. copy formula over and down to emulate your current table. once complete, copy new data, right click same selection and paste special/values. are the results different? if so, i'd copy your new values over your old ones as they probably have spaces.

Perfect! Worked for me as I was pondering how to clean up... Counts correctly now, thanks!