COUNTIF cells on a range that differ from those of another range based on a formula

1
votes

Golf scorecard

What I'm trying to achieve is to count the number of cells on a range that differ from cells on another range by a given difference.

In the example: how many double bogeys (none), bogeys (1), pars (2) and birdies (1) there are in a scorecard. For counting, say, Bogeys, this would obviously work, but it's a ridiculous formula:

=COUNTIF(B10;B9+1)+COUNTIF(C10;C9+1)+COUNTIF(D10;D9+1)+COUNTIF(E10;E9+1)

I've tried many combinations of COUNTIF, COUNTIFS, MATCH... functions, but all return syntax errors.

Help would be appreciated.

Thank you.

1
excel-formulacountif

1 Answers

1
votes

Use SUMPRODUCT:

=SUMPRODUCT(--($B$10:$E$10-$B$9:$E$9=3-ROW(A1)))

enter image description here The 3-ROW(A1) creates the sequence 2,1,0,-1 when dragged down.