CODEWITHSUNDEEP
excelexcel-formula
Jason Sykes
Jason Sykes

Reputation: 3

EXCEL sumproduct formula not counting correctly

I have a formula in EXCEL 2013 that counts values where the decimal point = .16

=SUMPRODUCT(--(MOD(D2:D9,1)=0.16))

so for example 2.16, 15.16 will be a count of 2. However if the value is 32.16 or greater then will not count. This is very weird issue and cannot fathom this out.

Upvotes: 0

Views: 770

Answers (1)

user4039065
user4039065

Reputation:

You've hit a floating point error¹. The remainder reads as 0.159999999999997, not 0.16. Round it to at least four decimals to get an accurate read.

=SUMPRODUCT(--(ROUND(MOD(D2:D9,1), 4)=0.16))

¹ See 15 digit precision floating point errors and Floating-point arithmetic may give inaccurate results in Excel.

Upvotes: 1

Related Questions