0

Tryingto CountIf with Exclusions and multiple criteria, Here's an example of just multiple critera:

=SUM(COUNTIFS(A1:A9,"YES",B1:B9,{"JOHN","GEORGE","RINGO","PAUL"}))

Here's an example of just exlusion:

=SUM(COUNTIFS(A1:A9,"YES",B1:B9,"<>*JOHN*"))

And here's the sum we're currently got but not working:

=SUM(COUNTIFS(A1:A9,"YES",B1:B9,{"<>*JOHN*","<>*GEORGE*","<>*RINGO*","<>*PAUL*"}))
TimandTed
  • 3
  • 7
  • The last part of your `COUNTIFS` is working like an `OR` statement, so in the first example, it returns true if the value in B# is JOHN *or* GEORGE *or* RINGO (and other conditions are met - A# = YES), and so on. Now apply that logic to your last example - if B# = NOT JOHN or NOT GEORGE, etc, then it will return true. The only time this would happen is if you have ALL of those keywords in B# and A# = YES. – CactusCake Nov 19 '15 at 15:53
  • Instead you might try something like `=SUM(COUNTIFS(A1:A9, "YES", B1:B9, "<>*JOHN*", B1:B9, "<>*GEORGE*", B1:B9, "<>*RINGO*", B1:B9, "<>*PAUL*")` which works as B# does not include JOHN *and* does not include GEORGE *and* does not include RINGO, and so on... – CactusCake Nov 19 '15 at 15:55

1 Answers1

0

When criteria becomes too complex for COUNTIFS, you can often use an array formula. I think the following array formula will achieve your goal...

=SUM(IF(A1:A9="YES",1,0)*(IF(ISNUMBER(FIND("JOHN",B1:B9)),0,1))*(IF(ISNUMBER(FIND("GEORGE",B1:B9)),0,1))*(IF(ISNUMBER(FIND("RINGO",B1:B9)),0,1))*(IF(ISNUMBER(FIND("PAUL",B1:B9)),0,1)))

Make sure to use CTRL+SHIFT+ENTER to enter the array formula.

Bobby Orndorff
  • 3,265
  • 1
  • 9
  • 7
  • Thank you for this formula, but we've got 50 words that we need to exclude, can you think of any other way? – TimandTed Nov 19 '15 at 15:40