COUNTIFS with string comparison of <> ?

[shell_l_d]

Here is a basic scale of what I'm actually trying to do... multiple (3 or 4) criteria in COUNTIFS(...) but need one of the criteria to not count if the string criteria is NOT a certain value, ie: to check a string is NOT or ! or <> a certain value, but I can't seem to get it to work. Hope this makes sence.

Eg: Table1:
Str1.....str2
is one....myA
is one....myA
not one....myA
not one....myB
is one....myB
is one....myC
is one....myC

Eg: summary data:
..........'is one'....'not one'... ! 'is one'
myA .... 2 .... 1 ..... ???
myB .... 1 .... 1 ..... ???
myC .... 2 .... 0 ..... ???

'is one' formula =COUNTIFS(Table1[Str1],"is one",Table1[str2],D11)
'not one' formula =COUNTIFS(Table1[Str1],"not one",Table1[str2],D11)
'! is one' formula, dont know how to do this.

I've tried these, but they don't work, error in formula:
=COUNTIFS(Table1[Str1]<>"is one",TRUE,Table1[str2],D11)
=COUNTIFS(Table1[Str1]<>"is one","TRUE",Table1[str2],D11)
=COUNTIFS(Table1[Str1],<>"is one",Table1[str2],D11)
=COUNTIFS(Table1[Str1],!"is one",Table1[str2],D11)

 

[RoryA]

I think you mean:
=COUNTIFS(Table1[Str1],"<>is one",Table1[str2],D11)

 

[Misca]

I'm using 2003 so I can't use COUNTIFS but usually you solve this kind of problems by taking away the ones that match your criteria from the total:

=COUNTA(range)-COUNTIF(range,criteria)

In your case the usual COUNTIF won't work because you have more than one criteria. That's why I have to replace the simple COUNTIF with SUMPRODUCT:

=COUNTA(range)-SUMPRODUCT(--(range1=criteria1),--(range2=criteria2))

 

[RoryA]

The OP must be using 2007 or later due to not only the COUNTIFS but also the Table style references.

 

[shell_l_d]

Perfect thanks Rorya, that works a treat.

I didnt think to try that as thought I had to put the quotation marks around my string "is one".