Count number of cells in a range where values differ by 1, 2 or 3

[Barking_Mad]

Hi,

I've got two ranges (A1:E1) and (A2:E2) which contain single digit numbers.

I have a formula to count how many values are equal to each other in both ranges

=SUMPRODUCT(--(A1:E1=A2:E2))

I'd now like to find out how many times the values in A2:E2 are less than/more than A1:E1 by 1,2 or 3.

Can anyone help?

Thanks

[jason.b75]

For a difference of 1

=SUMPRODUCT(--(ABS(A1:E1-A2:E2)=1))

Change the last digit for other differences.

Or to get the total of all differences in one formula

=SUMPRODUCT(--(ABS(A1:E1-A2:E2)={1;2;3}))

[davsth]

=SUMPRODUCT(--(A1:E1=A2:E2)-1)
=SUMPRODUCT(--(A1:E1<A2:E2)-1)


etc you get the idea or the previous post if you dont care about the direction of the difference

[Barking_Mad]

Cheers guys. I was on the right track, but as usual by brackets were in the wrong places!

[jason.b75]

=SUMPRODUCT(--(A1:E1=A2:E2)-1)
=SUMPRODUCT(--(A1:E1<A2:E2)-1)

Those formulas will not give the results that you think, the brackets are all wrong and will result in flipped polarities.

First one will give result of =SUMPRODUCT(--(A1:E1<>A2:E2))
Second on will give result of =SUMPRODUCT(--(A1:E1>A2:E2))

But both will return the result as negative

[Barking_Mad]

Hmmm maybe Im misunderstanding something or I didnt explain it very well.......

4 3 5 4 4 4 4 3 5 4 3 4 4 5 4 3 4 4
4 2 3 4 4 3 5 3 5 3 3 5 3 5 4 3 4 4

=SUMPRODUCT(--(ABS(A1:R1-A2:R2)=1))

Should give a result of 4, but actually gives it as 6. Edit: So it's summing the differences, I just want to know how many times a number is below a specific value.

(if it helps any these are golf scores, with the top row being the par and the row underneath being the score. I'm trying to work out how many scores are birdie (-1), eagle (-2) bogie (+1) double bogie (+2)

[Barking_Mad]

I think I sorted it. With the following as an array formula

{=COUNT(IF((A1:R1-A2:R2)=1,A1:R1))}

[davsth]

Jason bad positing you are right, it was not what I had written in the spreadsheet, hangs head in shame!

the result is that the formula is also looking at the other way

4 3 5 4 4 4 4 3 5 4 3 4 4 5 4 3 4 4
4 2 3 4 4 3 5 3 5 3 3 5 3 5 4 3 4 4

so together they are 6
=SUMPRODUCT(--(A1:R1=A2:R2-1))
=SUMPRODUCT(--(A1:R1-1=A2:R2))

get you to the 6 calculated

[jason.b75]

=SUMPRODUCT(--(ABS(A1:R1-A2:R2)=1))

Should give a result of 4, but actually gives it as 6. Edit: So it's summing the differences, I just want to know how many times a number is below a specific value.

(if it helps any these are golf scores, with the top row being the par and the row underneath being the score. I'm trying to work out how many scores are birdie (-1), eagle (-2) bogie (+1) double bogie (+2)

To get either / or, simply remove the ABS function from the sumproduct formula

=SUMPRODUCT(--((A1:R1-A2:R2)=1))

It is not summing the difference, but counting both 1 under and 1 over, as you asked for more/less than in your original question the formula was tailored for both.

Bearing in mind the golf scenario, I would reverse the formula to

=SUMPRODUCT(--((A2:R2-A1:R1)=-1))

To give the correct count of birdie / 1 under par, then adjust the criteria for other scores.

If your scoring tally goes from 2 over par to 'hang up your clubs' without counting the individual 3 over, 4 over, etc. then you could use

=SUMPRODUCT(--((A1:R1-A2:R2)>=3)) for 3 over or more.