CountIf but not if ???

[Need~help]

Hi, I wrote this to sum # of cells containing numbers or letters. =COUNTIF(E4:E16,"**"). How do I write it to ignore cells with the word "train" in it? Thanks

 

[werdout]

Maybe put an AND function in there as well? =COUNTIF(E4:E16, AND("**",NOT("train")))

 

[Weazel]

maybe...

=COUNTIF(I11:I13,"<>TRAIN")

 

[borgem]

Try =COUNTA(E$:E16)-COUNTIF(E4:E16,"*train*")

 

[gsistek]

maybe...

=COUNTIF(I11:I13,"<>TRAIN")

Unfortunately, that will count blank cells as well.

If you want to ignore cells with train anywhere in the cell, you could try:
=COUNTA(E4:E16)-COUNTIF(E4:E16,"*train*")

To only ignore cells with exactly "train", use:
=COUNTA(E4:E16)-COUNTIF(E4:E16,"train")

 

[Weazel]

Good point,

Though I assumed the poster wanted to include blanks in the count based on the logic that a blank in the range would not contain train and should be included in the count.

Either way, I appreciate you mentioning it.

 

[Need~help]

maybe...

=COUNTIF(I11:I13,"<>TRAIN")

This one did count all of the 13 cells in the array. Only two of the cells in the column were populated. Thanks

 

[Need~help]

This one looks promising but I could not get it to work whichever way I tried. Thanks

 

[Need~help]

Unfortunately, that will count blank cells as well.

If you want to ignore cells with train anywhere in the cell, you could try:
=COUNTA(E4:E16)-COUNTIF(E4:E16,"*train*")

To only ignore cells with exactly "train", use:
=COUNTA(E4:E16)-COUNTIF(E4:E16,"train")

I tried no avail. The first one came back with 4 as an answer for both columns
The second comes back with an error which I can't fix. Correct answers were 7 and 2 for Jan 28 and Feb 18. Below is a shot of the spreadsheet. I am trying to add up the columns to show me # of populated cells without the word "train" anywhere in it. Thanks Tom

21-Jan	28-Jan	4-Feb	11-Feb	18-Feb
Fi - D
	N - D
N - D	N - D
	R - D
	R - D
	V/4400 - D			V-train
			EX-train
N - D	N&V - D			V-train
Fi - D
	N - D
4	7	0	1	2	0

<colgroup><col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2673;width:56pt" width="75"> <col style="width:49pt" span="2" width="66"> <col style="mso-width-source:userset;mso-width-alt:2360;width:50pt" width="66"> <col style="width:49pt" width="66"> </colgroup><tbody>
</tbody>

 

[borgem]

...Correct answers were 7 and 2 for Jan 28 and Feb 18...

Don't you mean 7 & 0 are the correct answers?...I copied/pasted your data into a blank spreadsheet and used =COUNTA(E2:E14)-COUNTIF(E2:E14,"*train*") and it produced the proper results (Left to Right: 4,7,0,0,0,0). Are you possibly using an explicit reference with $'s instead of without?