CountIF with single non condition

[PavithraSubba]

I'm trying to count all the values except one value in a column.Column having more than 5 different types of values,except one vlaues i wanna count remaining.The problem is when i use <> it is counting entire excel cells in a column

=COUNTIFS(ReportfromMSSP!I:I,"<>Cancelled").it is returning 1048550.but i'm having only 205 column

 

[thisoldman]

Try
=COUNTIFS(ReportfromMSSP!I:I,"<>"&"Cancelled")

 

[Rick Rothstein]

Try this formula instead...

=COUNTIFS(Sheet1!I:I,"<>Cancelled",Sheet1!I:I,"*")

 

[mfexcel]

Rick's solution should do, as always

btw, when you know you have only 205 rows. Why not using I1:I205?

Anyway, here's my suggestion:
=COUNTA(I:I)-COUNTIF(I:I,"Cancelled")

 

[PavithraSubba]

this is not working.

 

[PavithraSubba]

205 is not a standard,data is dynamic.

 

[PavithraSubba]

Can you explain me what * will do in this formula.i have tried this.

 

[mfexcel]

205 is not a standard,data is dynamic.

There are many ways to make the formula a dynamic one. If you are using Excel 2007 or later, you may use Table to make your formula dynamic easily...

 

[Aladin Akyurek]

I'm trying to count all the values except one value in a column.Column having more than 5 different types of values,except one vlaues i wanna count remaining.The problem is when i use <> it is counting entire excel cells in a column

=COUNTIFS(ReportfromMSSP!I:I,"<>Cancelled").it is returning 1048550.but i'm having only 205 column

Better to avoid whole columns processing. That said:

=COUNTIFS(ReportfromMSSP!I:I,"?*",ReportfromMSSP!I:I,"<>Cancelled")

might be what you are looking for.

 

[mfexcel]

Can you explain me what * will do in this formula.i have tried this.

* is a wildcard for any series of character. Literally, it means any cells non-blank in Rick's formula.

btw, did you try my formula? it works.