Countif Not Equal to Multiple Criteria

[Will85]

On sheet 1 I have a data set of home values and the zip code in which the home is located, zip codes can be repeated.

Column A is the list of zip codes, column B is the homes value, the list is a couple hundred rows.

On sheet 2 I have a list of specific zip codes that I am interested in, about 20.

A1:A20 contain my list of specific zip codes I am interested in tracking. Columns B, C, D all have formulas to count the number of homes from Sheet 1 that both matches the zip code in column A as well as if the value of the home is less than 499k, 500k-999k, 1m-1.5m, etc.

But my list of 20 specific zip codes is not representative of the entire data set from sheet 1.

I would like a formula to countif any zip code not contained in A1:A20 falls into the home value buckets of columns B, C, D.

I know I could individually exclude each one of the 20 specific zip codes in a countif formula, but I assume there has to be a better way.

 

[jasonb75]

The easiest way is to count all of the values in the bucket while ignoring the zip codes, then subtract the count of values that do match the listed zip codes.

Assuming that you already have formulas returning counts for the listed codes then the existing results can be used for that part.

 

[Will85]

Thank you, this works.