Count instances where the difference between two columns is a specific value

[JayUSA]

Hi Everyone,

I seem to have forgotten my advanced Excel / CSE magic!

I have two columns of numeric data: A1-A20 and B1-B20

I want to COUNT (not sum) the number of times that:

the A value in the column, less the corresponding B value beside it, is exactly 3

PROVIDED that both A and the B values are positive (i.e. greater than zero).


Any suggestions, folks?

Cheers,

Jay

[ChemistB]

Try

=SUMPRODUCT(--(B1:B20-A1:A20=3))

[mrice]

Try this array formula

=SUM(IF(A1:A20-B1:B20=3,IF(A1:A20>0,IF(B1:B20>0,1,0),0),0))

Remember to press control- shift - enter when entering this into an empty cell.

[JayUSA]

Well... I reversed the subtraction logic to comply with scenario I was trying to describe, giving:

=SUMPRODUCT(--(A1:A20-B1:B20=3))

However, in my spreadsheet, this formula incorrectly counts cell pairs of difference =3, even when the A or B value is blank.

Thus two A/B cell pairs of:

3 [blank]
[blank] -3

will yield a count of 2, where it should yield a count of zero.

Perhaps a further tweak will make it work as I am hoping?

Cheers,

Jay

[ChemistB]

Okay, try this

=SUMPRODUCT(--(A1:A20-B1:B20=3), --(A1:A20<>0),--(B1:B20<>0))

[JayUSA]

Yes, that works beautifully!

[JayUSA]

[My error.]