select column base on criteria

[doggies]

hi, is there anyway that I could select a whole column base on a
criteria so that I can count the cells that contain >0 in that column?

eg
abc def ghi
1 7
2 5 8
3 6 9

for criteria=def, column 2 is selected and 2 will be returned.

tks

 

[JE McGimpsey]

One way:

Assuming your column headers are in row 1 (array-entered:
CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(IF(OFFSET(A1,1,MATCH(crit,1:1,FALSE)-1,65535,1)>0,OFFSET(A1,1,MATC
H(crit,1:1,FALSE)-1,65535,1),""))