is there a formula to count the values in a column which give an absolute value result of 1 when the values in another column are subtracted

Question

Abt. 30 integer values in range say 1 to 1000 fill one column and same is true for a second column. Some of these numbers give an absolute value of 1 when subtracted from each-other. Say 587 is in one column and 588 in the other. |587-588|=1. I need to find how many of these numbers are in one of the two columns.

Without a formula I have to try 30x30=900 times for each number in one column, store the conditional result as say 1/0 and count the ones but it is tedious.

Answer 1

Using COUNTIFS:

=(COUNTIFS($B$1:$B$30,A1+1)+COUNTIFS($B$1:$B$30,A1-1))>0

If you need the exact number:

=SUM(COUNTIFS(B:B,">="&A1:A30-1,B:B,"<="&A1:A30+1,B:B,"<>"&A1:A30))

which would return 2 with the above sample data.