CODEWITHSUNDEEP
excelcountifsumproduct
Klov
Klov

Reputation: 5

Excel: CountIf RangeA is n units larger than Range B

I want to count the number of times values in RangeA exceed the values in RangeB by n units; only comparing values in the same row.

=SUMPRODUCT(--(RangeA < RangeB))

works as a start, but I want to be able to change the tolerance ie. 

=SUMPRODUCT(--(RangeA < (RangeB + n))

Upvotes: 0

Views: 31

Answers (1)

Scott Craner
Scott Craner

Reputation: 152650

Your formula works for me:

=SUMPRODUCT(--(A1:A4<B1:B4+2))

enter image description here

It appears that the issues is the title row. Start at Row 2 and use a dynamic end:

=SUMPRODUCT(--($A$2:INDEX(A:A,MATCH(1E+99,A:A))<$B$2:INDEX(B:B,MATCH(1E+99,B:B))+2))

This will now start at row 2 and make a dynamic range of only the data.

enter image description here

Upvotes: 1

Related Questions