Count unique text values within a range based on another column

Hi all,
I've searched the board for this one and I have not had any luck. I would like to count the number of unique text values in column (B) that fall within a specified range of column (A). For example...

A B
(Depth) (Fish)
2 HFBD
4 BLK
6
8 BLK
10
12 BLU

Given the table above, count the number of unique species in column B that are at a depth greater than or equal to two (2) and less than or equal to six (6). Note that Column A is continuous but B has some blanks.

My actual data set spans hundreds of meters and species of fish...so generating this function is key. Ultimately, I want to count to number of unique values by specifying a depth range in another column.

I am using Excel 2010

Thank you in advance.
Cheers

{UPDATE}
I've included a sample data set.

Hi,

to be confirmed with control+shift+enter

Please Login or Register  to view this content.

Regards

Hi,

again a small patch to exclude from counting blanks

Please Login or Register  to view this content.

Cheers

Hi.

I think you probably need to resolve blanks in column B as well.

Array formula**:

=SUM(IF(FREQUENCY(IF(B2:B100<>"",IF(ABS(4-A2:A100)<=2,MATCH(B2:B100,B2:B100,0))),ROW(B2:B100)-MIN(ROW(B2:B100))+1),1))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

I can't seem to see your attempts at adapting the solutions provided in that file? Did you upload the correct one?

Regards