find text and count word 2 cells beside

[David GG]

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="OUT")) ???
??? =COUNTIF(B:B,B10,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

 

[Roger Govier]

Hi David

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

 

[David GG]

Thanks Roger it worked
Stupid question though what do the -- do in the formula?

 

[Roger Govier]

Hi David

The double nary minus (--) is one way of coercing the True/False result from
the tests to 1/0 so they can be summed in Sumproduct.

B1=B10 would return True or False let's assume False
D1="OUT" would return True or False let's assume True
so we would get
0 * 1 = 0
If for B2 and D2 we got True and True we would get
1 * 1 = 1

So, only when both conditions are True (1) would we get a result of 1, all 3
other combinations would return 0 so Sumproduct just sums all these 0's and
1's to give the number of cases when both tests are True.