Calculate multiple rows of data then sum the results

[-Wayner-]

Hello,

I have a feeling this may be a relatively simple one, but as someone who just never quite gets array formulas I'm struggling to work it out!

I'd like to evaluate a series of subtractions and then count the results. So I have two columns of data, each of containing 10 rows (as an example), each cell containing an integer between 1 and 10. For each row I'd like to subtract one cell from the other on that row (A1 - A2, B1- B2, etc.) then do a count based on the results at the end.

So count of results from the 10 calculations that are less than 0, between 1 and 2 etc.

I know I could do this by doing the calcs in a separate column then counting on the third column, but I feel it should be do-able in one place!

Appreciate any advice on this.

Many thanks,
Wayner

 

[steve the fish]

This one would work for less than 0:

=COUNT(IF(A1:A10-B1:B10<0,))

Or between 1 & 2 inclusive:

=COUNT(IF(A1:A10-B1:B10>=1,IF(A1:A10-B1:B10<=2,)))

 

[steve the fish]

Or these which don't need array entry:

=SUMPRODUCT(--(A1:A10-B1:B10<0))
=SUMPRODUCT(--(A1:A10-B1:B10>=1),--(A1:A10-B1:B10<=2))

 

[-Wayner-]

Both work fantastically - thank you for both of the quick and effective responses