Reverse COUNTIF / COUNTIFS

J. Craig Williams

 

I have a list of 200 items.

I want to count the ones that are NOT "Jimmy"

 

=COUNT([columnA]:[columnA] - COUNTIF([columnA]:[columnA],"Jimmy")

 

But I have a second column that I need to know the NOT "Jimmy".

 

what I would like is 

 

COUNTIFS(   not this::: ([columnA]:[columnA],"Jimmy"), and this ([columnB]:[columnB],"Maryland") )

 

or count the number of items in my list that are in Maryland but not named "Jimmy"

 

I think I need a new column (which I have working), but am hoping somehow to put that NOT in the COUNTIFS somehow.

Nothing I have tried has worked.

 

Craig

Dietrich Koch

Hi Craig,

 

funny - it looks like we were struggeling with the same problem at the same time ;-) 2min after your question I came up with a similar one...

 

I also did not manage to include the NOT function within a count/countif, since I thought it would solve my problem.

 

You mentioned, you solved it by using a second column. Could you give me a hint what you did in this 2nd column?

 

Best 

Dietrich

J. Craig Williams

Dietrich,

 

Often I just move the formula to the another row and get a true value.

I sometimes call them "CalcCol" or "ChkCol" so I remember they are there.

 

so, moving this

( not this::: ([columnA]:[columnA],"Jimmy"), and this ([columnB]:[columnB],"Maryland") )

 

=IF(AND(NOT([columnA]23)="Jimmy"),[columnB]23="Maryland"),1,0)

 

I typed that here, hope it works.

Then all the columns have check boxes checked when I need them to.

The COUNTIF becomes a COUNT because I've already done the IF.

 

Craig

Jim Hook

I've run into the same issue and ended up using another column to perform the logic needed. I don't think the count functions allow logic in the field you are trying to find matches for.

Dietrich Koch

Craig,

 

thanks for your explanations!

Have to think about it :-)

 

Best 

Dietrich

Travis

I believe the only way to do this is with a second column. I asked around and the census is a second column is needed but I will keep thinking about it!  

Travis

Hey Craig! My colleague (Sue) figured it out! 

 

Count the number of times "Maryland" appears, then subtract the number of times "Jimmy" and "Maryland" appears. The result is the number of times "Maryland" is in one column and NOT "Jimmy" in the other. 

 

=COUNTIF(Primary:Primary, "Maryland") - COUNTIFS(Text:Text, "Jimmy", Primary:Primary, "Maryland")

 

 

J. Craig Williams

Great Travis! Thank her for me.

 

Craig

Allen ZHAO

Hi Craig

 

I am new to SmartSheet, hope my thought may help.

What i normally used in Excel for the same situation is
=COUNTIFS(Column:Column, "<>Jimmy", Column:Column, "Maryland")

Maybe you can have a try to see whether it may work.

 

Thanks

 

Allen

J. Craig Williams

Allen,

 

That actually worked!

I'm amazed and delighted! Thank you.

My mind is spinning. 

 

Craig

Allen ZHAO

Hi Craig

Thanks for letting me know.

Glad to hear it is working.

 

Allen