count on unique

[jw01]

Im having some trouble trying to do a count on a code

column D list locations.
column A list the code.

basically the value in column A shows once and under it, the locations are listed (column d)

im trying to do a count to see how many times the city name occurs under a particular code (see column H) in the attachment.

can someone pls help...im not sure how to approach this :S

[DGagnon]

try this, it is looking for any value after it finds the number you are looking for, thus counting the number of blank lines, and giving you you total number of entries under a heading in A.

=MATCH("*",INDIRECT("A"&MATCH(G4,A:A,0)+1&":A10000"),0)

let me know how it works.

[vlady]

there are colored cells is that were the formula should be??

if that's the case you can use this..
this for the first colored cell

=COUNTA(D2:D3)-COUNT(D2:D3)
change the range... as per code..
even if you insert cells it will count it, and counting values only spaces and numbers are not included..

[jw01]

try this, it is looking for any value after it finds the number you are looking for, thus counting the number of blank lines, and giving you you total number of entries under a heading in A.

=MATCH("*",INDIRECT("A"&MATCH(G4,A:A,0)+1&":A10000"),0)

let me know how it works.

that works really smooth wow...how is this working but genius!

[DGagnon]

The second match is looking for the value you want to count, once it finds it, that is being used to create a new range for the first match formula to use in looking for the next row that has an entry. Once it finds that, it outputs it as a number which happens to be the number you are looking for.

A good challenge, and im happy i could help you.