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!

 

[Zack Barresse]

hello, welcome to the board!!!

you may want to check for preceding or following spaces in your selections. to give you an example...

Farm1 04-05Fresh.XLS
	N	O	P	Q
220	y	y	y	1
221	normal	spaceprior	spacefollowing
MAY

if your looking to do that in a function, check out the TRIM function.

hth

 

[fairwinds]

Maybe you need

=SUMPRODUCT(--(A1:A10={"y","n","n/a"}))

 

[cleveland_sara]

I don't think that the SUMPRODUCT will work because I need to keep the totals for each "y", "n", and "n/a", as well as all answers together (total of reports per column).

The problem is really that I think that somehow have dead cells or a glitch in the sheet that is making a few select cells very fussy about reporting correctly. Has anyone else encountered this?

Thanks for any assistance.

Regards,
Sara

 

[Zack Barresse]

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.

 

[Juan Pablo González]

The problem is really that I think that somehow have dead cells or a glitch in the sheet that is making a few select cells very fussy about reporting correctly. Has anyone else encountered this?

No... as firefytr said, you have something strange within those cells. The problem is not the COUNTIF, is the data that it is checking... if you have cells with "n", "n ", " n", COUNTIF(A1:A3, "n") would only count the first one.

 

[GorD]

How about in an adjacent column use the =len(a1)function to see if the number of characters you think are in the cell rally are. This should highlight to you any hiding additional spaces.

Like others said it is the data not the formula

 

[cleveland_sara]

I tried what Zack suggested about using the TRIM function and copying values, but I got the same results. I had previously gone through to make sure there were no spaces, etc. I double checked the formulas just now and they are typed correctly, i.e. no spaces, etc. The formula counts the other "n" rows in the column, just not for a few of the rows. Further, it seems to be only specific columns affected. My data range is about 500 rows and 11 columns. It is about four rows and sporadic columns within these rows that are problems.

Thanks for replying so quickly - I appreciate all the help I can get as I am about ready to chuck the computer out the window!

Regards,
Sara

 

[Zack Barresse]

one question:

is there water below your window or solid ground? as i'm trying to get a visual kidding, hehe, i've been there!!

would you mind posting what your data looks like with the HTML Maker? a link can be found at the bottom of this page. the 'About This Board' forum has some good posts on helping to install it.

 

[cleveland_sara]

Below the window is a light well, but I don't think I can open the window anyhow (an the window is in an office that is currently locked and I am in a cubicle!).

Thanks for all the help. I (we) got it working! There were spaces in those cells as many of you said, but they would not go away without first using the TRIM function, which I had never heard of - I am a relative newbie. The len function let me see the spaces. Thank you all so much - I am getting correct totals.

This forum is great - thanks again for the help!

Sincerely,
Sara