Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Reverse COUNTIF / COUNTIFS
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
Comments
-
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
-
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
-
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.
-
Craig,
thanks for your explanations!
Have to think about it :-)
Best
Dietrich
-
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!
-
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")
-
Great Travis! Thank her for me.
Craig
-
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
-
Allen,
That actually worked!
I'm amazed and delighted! Thank you.
My mind is spinning.
Craig
-
Hi Craig
Thanks for letting me know.
Glad to hear it is working.
Allen
