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]

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

Just to make sure:

=COUNTIF(ReportfromMSSP!I:I," ")

What does this yield?

 

[PavithraSubba]

it is returning 0

 

[Aladin Akyurek]

it is returning 0

What do yopu get with:

=SUM(COUNTIFS(ReportfromMSSP!I:I,{"InProgress","","On Hold","Completed"}))

=COUNTIFS(ReportfromMSSP!I:I,"Cancelled")

 

[PavithraSubba]

for 1st one i got 1048548

2nd one,i got 27.

1st includes entire column cells just bcoz of "",when i remove this it shows 112.

my column cells were 205 and it is dynamic

 

[Aladin Akyurek]

for 1st one i got 1048548

2nd one,i got 27.

1st includes entire column cells just bcoz of "",when i remove this it shows 112.

my column cells were 205 and it is dynamic

I meant:

=SUM(COUNTIFS(ReportfromMSSP!I:I,{"InProgress","On Hold","Completed"}))

 

[PavithraSubba]

I got result for =SUM(COUNTIFS(ReportfromMSSP!I:I,{"InProgress","On Hold","Completed"}))

but i wanna count blank cells also

 

[Aladin Akyurek]

I got result for =SUM(COUNTIFS(ReportfromMSSP!I:I,{"InProgress","On Hold","Completed"}))

but i wanna count blank cells also

Hmm...

What do you have in ReportfromMSSP!I1? A header maybe?

 

[mfexcel]

I got result for =SUM(COUNTIFS(ReportfromMSSP!I:I,{"InProgress","On Hold","Completed"}))

but i wanna count blank cells also

Do you know how to use Table? or create a NamedRange?

 

[mfexcel]

I think the easiest way for your question is 1st to create a Table

1) Go to ReportfromMSSP!I1
2) On the Ribbon, Go to Insert --> Table
3) Confirm the range of the data for your table; check "My table has headers" if applicable
4) OK

Now input the following formula
=COUNTIF(Table1[Status],"<>Cancelled")
Table 1 is the Name of the table you have just created
[Status] is the header of the column showing status. Pls change it according to the header name on your table.

Table expands and shrinks automatically when you add new rows/columns or delete existing rows/columns.

Hope it helps.

 

[mfexcel]

If you prefer formula still, try this:

=COUNTIF(ReportfromMSSP!$I$2:INDEX(ReportfromMSSP!I:I,MATCH(REPT("z",255),ReportfromMSSP!I:I)),"<>Cancelled")
If you do NOT have header, change I2 to I1.

The dynamic formula (Match(REPT("z"...) is from Aladin too.
http://www.mrexcel.com/forum/excel-questions/67536-last-row-data-formula.html

One limitation: The last entry in the status column cannot be a number, otherwise it won't be counted.