Stupid excel question

February 13, 2009 1:34 PM

Excel question: Need formula to COUNT cells WHERE text is NOT "foo" or "bar"

This would be so simple in SQL but I can't figure out the formula in Excel. Let's say I have one column whose entries are mostly "foo" and "bar", but also miscellaneous stuff like "tator", "fedora", and some blank cells. I want to group the other values into an "other" category, but I won't always be able to predict what they are.

This is a really small spreadsheet (less than 100 rows) so I don't want to bother with Access.

posted by desjardins to computers & internet (16 answers total)

You want an if statement. An if statement in excel goes =if(equation=true,this,otherwise_this)

So you'd want =if(A1="foo","foo","other").

If you want to include bar as well, you could double up with =if(A1="foo","foo",if(A1="bar","bar","other"))

With the second if-statement only being referenced if A1 is not foo.
posted by shaun uh at 1:45 PM on February 13, 2009

Well COUNTIF could probably do it but as far as I know it can only take 1 criterion

So:

SUM(IF(Cell1:CellN<>"foo",IF(Cell1:CellN<>"bar",1,0),0))
posted by jckll at 1:45 PM on February 13, 2009

Okay, misread the question a little. That would create a separate column. If you just want one cell which is giving you the count of the non-foo/bar entries then do countif:

Option A:
Make the separate column with non-foobars marked "other" and then make a countif cell:
=countif(A1:A100,"other")

Option B:
Make four cells, one a countif for foo, another a countif for bar, a count for all the cells, and subtract the first two from the third to get the total # of other.

Also, are you trying to ask how to count the non-foobars but not the blank cells? Because there's yet another answer for that one.
posted by shaun uh at 1:50 PM on February 13, 2009

So basically I need to create column B that tells me if the value in column A is foo, bar, or other? and then count the "others" in that column? I guess that's simple enough but I was hoping for a one-cell solution.
posted by desjardins at 1:54 PM on February 13, 2009

Make four cells, one a countif for foo, another a countif for bar, a count for all the cells, and subtract the first two from the third to get the total # of other.

goddamnit. I knew I was overthinking this.
posted by desjardins at 1:56 PM on February 13, 2009

=COUNTA(A1:A8)-COUNTIF(A1:A8;'foo')-COUNTIF(A1:A8;"bar")
(oocalc style)
posted by fleacircus at 1:57 PM on February 13, 2009

combine a couple of functions:
COUNTIF
AND
IF
<> (not equals)

to get something like this:

COUNTIF(AND(IF(A1<>"foo";TRUE),IF(A1<>"bar";TRUE)))

(you may have to make minor syntactic changes - I'm used to using Open Office)
posted by chrisamiller at 2:01 PM on February 13, 2009

fleacircus is close, but that formula undercounts, since it excludes blank cells
posted by found missing at 2:02 PM on February 13, 2009

Oh duh! I remember now. :)

You want to use a special type of sum that allows you to do multiple criteria and turns it into more of a count. This involves pressing control+shift+enter when you finish typing the formula.

I have to run, but see this website for details:
http://www.ozgrid.com/Excel/count-if.htm
posted by shaun uh at 2:04 PM on February 13, 2009

=(COUNTIF(A1:A6,"<>neverfound")-COUNTIF(A1:A6,"bar")-COUNTIF(A1:A6,"foo")), where neverfound is a text string that doesn't appear in your list.
posted by found missing at 2:07 PM on February 13, 2009

=SUM(N(A1:A6="foo"),N(A1:A6="bar"))

Press control+shift+enter after you input the formula.
posted by mullacc at 2:17 PM on February 13, 2009 [1 favorite]

that formula counts the foo bars, but not the number of cells not foo or bar
posted by found missing at 2:19 PM on February 13, 2009

whoops...that counts all the instances of foo and bar. Just stick a rows(A1:A6) in front and then subtract the result of the sum.
posted by mullacc at 2:22 PM on February 13, 2009 [2 favorites]

mullacc, I have no idea how you figured out how to use the N() function in that way, but You Da Bomb!

Thanks!
posted by IAmBroom at 2:29 PM on February 13, 2009

IAmBroom: It's really great when you want to sum or count numbers from multiple columns of data using more than variable--you use N() in conjunction with SUMPRODUCT().
posted by mullacc at 2:33 PM on February 13, 2009 [1 favorite]

er, more than one variable.
posted by mullacc at 2:33 PM on February 13, 2009 [1 favorite]