VBA - COUNTIF "not equal" to different criteria

[crayhons]

Hi all,

I'm looking for some help on creating a not equal to COUNTIF or SUMPRODUCT that will go through a specific column (G) and will check all the cells for each abbreviation for the 50 states. If a cell does not equal one of the 50 abbreviations, it will be counted.

If I need to explain more, please let me know. Any help would be great.

Thanks.

[stnkynts]

Try this custom function:

Please Login or Register  to view this content.

To use just go to whichever cell you want the count reported and insert =CountNonStates(G1:GX) where X is the last row. Or you could just do G:G if you want the whole column

Note: You will need to take the time to add all 50 states. I added 4 for you.

[crayhons]

Hmm. Didn't seem to work. Maybe I did something wrong, but it didn't end up counting the number of non-states.

Anybody else have any suggestions?

[crayhons]

Just bumping.

[stnkynts]

Hmm. It works just fine for me. What value are you getting back? Did you enable macros in the workbook?

[crayhons]

It's just giving me an error. I'll give you the code I have entered in my VBA and I'll try to explain it to you. Basically, I'm using this function / formula to count all the unknown / foreign states listed within a large census folder (e.g. anything not recognized as a state in the US).

Please Login or Register  to view this content.

I want the count to be placed in Cell G57, in the worksheet Initial Tester, under the Initial Demographics tab. Using the formula CountNonStates, in the worksheet Census Tester, under the Census tab, checking data from column G.

Thanks.

[mikerickson]

A formula like
=SUMPRODUCT(--ISNUMBER(SEARCH(C1:C100&",","AZ,CA,MN,"))*(C1:C100<>"")) should do this.

Note that the constant string "AZ,CA,MN," ends in a comma.
In practice, it would be easier to have a named string constant of all the state abreviations.

[crayhons]

A formula like
=SUMPRODUCT(--ISNUMBER(SEARCH(C1:C100&",","AZ,CA,MN,"))*(C1:C100<>"")) should do this.

Note that the constant string "AZ,CA,MN," ends in a comma.
In practice, it would be easier to have a named string constant of all the state abreviations.

Hi Mike, I tried your formula and as I was entering it in VB it kept giving me a "Expected end error" at the beginning where you have (C1:C100&",",").

[crayhons]

EDIT: double-post. oops.

[crayhons]

Anybody else have any ideas?

[mikerickson]

That is a worksheet formula. In VBA, try

Please Login or Register  to view this content.

[crayhons]

Hi Mike, I'll give it a go here in a little bit and get back to you.

Thanks.

[crayhons]

That is a worksheet formula. In VBA, try

Please Login or Register  to view this content.

Mike, I put this into my code and it ran through all the way, but instead of counting all the instances that weren't equal to the state abbreviations, it counted all that WERE a state abbreviation.

[crayhons]

Anybody else know how I can change the code to make it count the non-instances of the state abbreviations?

Thanks.

[crayhons]

Still without a solution if anyone else cares to take a crack at it.

Thanks

[mikerickson]

If the formula counted the "has abbreviation" why not subtract that number from the total number of rows in the range?