Hi David

=SUMPRODUCT(--($B$1:$B$1000=B10),--($D$1:$D$1000="OUT"))

--
Regards
Roger Govier

"David GG" <David wrote in message
...
have a word in cell B10 (BOX195) and want to find all the BOX195 in column
B:B and count how may have the word OUT in the column D. Note it is a big
database with lots of different BOX numbers.

??? =COUNTIF(B:B=B10,and(D:D="OUT")) ???
??? =COUNTIF(B:B,B10,D:D="OUT") ???

Column A Column B Column C Column D
1 BOX085 bbb OUT
2 BOX195 BBB OUT
3 BOX102 bbb OUT
4 BOX195 BBB IN
5 BOX102 bbb IN
6 BOX195 BBB OUT
7 BOX085 bbb IN
8 BOX195 BBB OUT
9 BOX085 bbb OUT
10 BOX195 BBB IN