another thought on COUNTIF 2 VARIABLES ??

[Alan]

Thanks for your help gents .. very much appreciated

Perhaps I am now getting ahead of myself, but If I wished to search this
spreadsheet and return all 3 digit reference numbers to another master
worksheet when column B was 1 or more ... is there a simple function to do
this or am I looking for a vb solution

once again many thanks for your assistance and paitence

Alan

 

[Biff]

Hi!

A1:A100 = 3 digit numbers
B1:B100 = 1 etc.

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100>1,ROW
($B$1:$B$100)),ROW(1:1)))

Copy down until you get #NUM! errors which menas the data
has been exhausted.

Biff