Match count multiple criteria

[gvanstone]

I am trying to find a formula that can return a count of matching postal codes in a range.
For example:
In row 1-> raw data of many postal codes (many cities)
In row 2-> set range of postal codes (for a given city)

Return value in row 3-> How many postal codes in raw data (row1) match the the set range of postal codes (row2)

Any thoughts?

[gvanstone]

BTW the amount of postal codes i am talking about is in the range of hundreds. This is not a match of 2 or 3 postal codes. So a lot of the formula i looked at involved manual entering every postal code which i hope i will not have to do.

[quekbc]

Try something like...

Please Login or Register  to view this content.

[gvanstone]

Thats what i am currently doing. Hoping there was another method that might be better.

[quekbc]

I might've misread the first post.

Are you looking to count the total number of postcodes in Row 1 that matches ALL of row 2?

i.e.
Row 1:1234, 2341, 3421, 4321
Row 2: 1234, 4321
Row 3: 2

[gvanstone]

Yes that is correct.

[quekbc]

In that case, try this.

Please Login or Register  to view this content.

then press Ctrl + Shift + Enter

(Ctrl + Shift + Enter = very important step)

[gvanstone]

I tried this and it returns value =0 which is incorrect I have true value of 362. Hmm.

[quekbc]

Hmm. This is odd.

Did you do the Ctrl shift enter thing?

[gvanstone]

yes. It put { } around the formula and returned a value of zero.

[quekbc]

hmm... that is odd...

is it possible for you to upload a sample file?

(zipped, as my company proxy stupidly blocks xls and xlsx files)

[gvanstone]

Here is a sheet of data.

[quekbc]

I tried the formula in your worksheet. Seems to be working fine, with the exception that instead of rows, the data is in columns. That might be causing the problem.

Please refer the attached file for clarification.

[gvanstone]

hmm.. i was using A:A and B:B which prob why.

[quekbc]

Interesting... I tried using A:A and B:B... it does give 0.

Guess that's the root of the problem.

Very interesting.

[gvanstone]

I would like to know the formula that performs the same operation as the Sort Ascending button.

[quekbc]

That requires clever programming (there's a whole topic on sorting in programming classes).

I'd recommend starting a new thread so others with better expertise can have a look at it.

Edit:

Try, Record Macro, sort the data, Stop Recording. and see what churns up.

[gvanstone]

record macro.. thanks i should have thought of that.