CountIF with single non condition

T

thisoldman

Well-known Member

Joined

Jan 5, 2014

Messages

1,075

• Mar 25, 2014

• #2

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

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,462

Office Version

1. 365

Platform

1. Windows

• Mar 25, 2014

• #3

Try this formula instead...

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

 

Upvote 0

M

mfexcel

Well-known Member

Joined

Jan 8, 2009

Messages

791

• Mar 25, 2014

• #4

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")

 

Upvote 0

P

PavithraSubba

New Member

Joined

Jan 23, 2014

Messages

29

• Mar 25, 2014

• #5

this is not working.

 

Upvote 0

P

PavithraSubba

New Member

Joined

Jan 23, 2014

Messages

29

• Mar 25, 2014

• #6

205 is not a standard,data is dynamic.

 

Upvote 0

P

PavithraSubba

New Member

Joined

Jan 23, 2014

Messages

29

• Mar 25, 2014

• #7

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

 

Upvote 0

M

mfexcel

Well-known Member

Joined

Jan 8, 2009

Messages

791

• Mar 25, 2014

• #8

PavithraSubba said:

205 is not a standard,data is dynamic.

Click to expand...

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

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 25, 2014

• #9

PavithraSubba said:

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

Click to expand...

Better to avoid whole columns processing. That said:

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

might be what you are looking for.

 

Upvote 0

M

mfexcel

Well-known Member

Joined

Jan 8, 2009

Messages

791

• Mar 25, 2014

• #10

PavithraSubba said:

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

Click to expand...

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

 

Upvote 0