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

 

[Aladin Akyurek]

* 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.

1.

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

2.

=COUNTA(I:I)-COUNTIF(I:I,"Cancelled")

3.

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

will all behave the same, except when the column contains formulas returning a blank, that is, "".
In the latter case, the third formula will differ. Also, last two will ignore non-text input, while the first will count in everything excep "Cancelled."

 

[Rick Rothstein]

Try this formula instead...

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

Sorry, I used my test sheet name instead of your actual sheet name and forgot to change it when I posted it. I see Aladin posted the formula using the correct sheet name, so use what he posted.

 

[PavithraSubba]

Yes,i gave try to you formula.but my column includes blank values also.

See,my column having "InProgress"," ","On Hold","Completed","Cancelled".

from the above values i have to count all values except cancelled.

I have tried like =COUNTIFS(ReportfromMSSP!I:I,{"InProgress","","On Hold","Completed"}) and =COUNTIFS(ReportfromMSSP!I:I,"<>Cancelled").

 

[PavithraSubba]

if i use * it is not counting blank value within the range

 

[PavithraSubba]

both 1st and 3rd behave same,not counting blank value within the range.i wanna count blanks also covered within the range.

2)i'm doing calculation in one sheet and input data were in another sheet,so only refered with sheet name.when i use like this =COUNTA(I:I)-COUNTIF(I:I,"Cancelled").will it work
i have tried this like =COUNT(ReportfromMSSP!I:I)-COUNTIF(ReportfromMSSP!I:I,"Cancelled"). Count and counta is not working

 

[Aladin Akyurek]

both 1st and 3rd behave same,not counting blank value within the range.i wanna count blanks also covered within the range.

2)i'm doing calculation in one sheet and input data were in another sheet,so only refered with sheet name.when i use like this =COUNTA(I:I)-COUNTIF(I:I,"Cancelled").will it work
i have tried this like =COUNT(ReportfromMSSP!I:I)-COUNTIF(ReportfromMSSP!I:I,"Cancelled"). Count and counta is not working

Do you have formulas in column I on ReportfromMSSP? If so, what is that formula?

 

[PavithraSubba]

i don't have formula in column I on ReportfromMSSP,original data were there in I column.

i'm doing caluculation in another sheet.Column I of ReportfromMSSP sheet is having values like "Cancelled,InProgress,Completed," ",On Hold"

 

[mfexcel]

@Aladin, you can always show me something new. I really have never thought about the case of "". You have just inspired to conduct a test on the formulas and they do behave a little different IF number is included in the range...

1.

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

Ignore BLANK (nothing input) and Number; Count ""

2.

=COUNTA(I:I)-COUNTIF(I:I,"Cancelled")

Ignore BLANK only; Count both "" and Number

3.

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

Ignore BLANK, "", and Number; Count Text only

will all behave the same, except when the column contains formulas returning a blank, that is, "".
In the latter case, the third formula will differ. Also, last two will ignore non-text input, while the first will count in everything excep "Cancelled."

 

[Aladin Akyurek]

i don't have formula in column I on ReportfromMSSP,original data were there in I column.

i'm doing caluculation in another sheet.Column I of ReportfromMSSP sheet is having values like "Cancelled,InProgress,Completed," ",On Hold"

Do not use space, that is, " ". Leave the cell empty...

 

[PavithraSubba]

yes cells were empty only,Can you please fill a column with values mentioned and try in another sheet with formula used.i have tried all formula told in this forum