COUNTIFS with string comparison of <> ?

shell_l_d

Board Regular
Joined
Jun 25, 2010
Messages
73
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)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
I think you mean:
=COUNTIFS(Table1[Str1],"<>is one",Table1[str2],D11)
 
Upvote 0
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))
 
Upvote 0
The OP must be using 2007 or later due to not only the COUNTIFS but also the Table style references. ;)
 
Upvote 0
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". :)
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top