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

<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>
[TD="class: xl64, width: 78"]21-Jan[/TD]
[TD="class: xl64, width: 75"]28-Jan[/TD]
[TD="class: xl64, width: 66"]4-Feb[/TD]
[TD="class: xl64, width: 66"]11-Feb[/TD]
[TD="class: xl64, width: 66"]18-Feb[/TD]
[TD="class: xl65, width: 66"] [/TD]

[TD="class: xl67"]Fi - D[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl65"]N - D[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl67"]N - D[/TD]
[TD="class: xl67"]N - D[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl67"]R - D[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl65"]R - D[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl67"]V/4400 - D[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]V-train[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]EX-train[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl67"]N - D[/TD]
[TD="class: xl67"]N&V - D[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]V-train[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"]Fi - D[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl67"]N - D[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]0[/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

</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?