counting number of cells in same row of two data ranges/columns with identical values. Excel

Question

I would like to count how often a number (>0) appears in the same row of both columns 1 and 2.

I.e. in the below example the answer should be 1 for value 19 in both cells A11 and B11 (but should ignore value 8 in cells A8 and B9).

I tried these formulas:

=COUNTIF(A11, "=" & B11) works for single cells, but extending it to =COUNTIF(A2:A18, "=" & B2:B18) returns 0 (Error message 'Formula omits adjacent cells).

I also tried =COUNTIF(A2:A18, "= B2:B18") which also returns 0. (Here B2:B18 is not recognised as a range).

column 1    column 2
0           0
0           0
X           X
6           X
6           14
7           X
8           X
12          8
16          X
19          19
21          16
29          16
31          13
36          23
94          58
189         104
193         112

I am using Office Professional Plus 2016.

Answer 1

Try this:

=SUMPRODUCT((A2:A18=B2:B18)*(A2:A18>0)*ISNUMBER(A2:A18))

Here, I am checking three conditions. (1) cells are equal (2) cell value is greater than zero and (3) cell value is a number. Change ranges in formula as required.

See image for reference: