Forum Discussion
Solved
Serious bug with certain decimal numbers: Countifs and Roundup failed
I have a file that had time upto the seconds level. Some of these numbers had 99999.. after the fifth decimal digit. And all these numbers seem to throw a major bug. I wanted to find out how many ...
In addition, Excel works by default with 15 digits and many functions ignore extra digits, but some work in background with 17 digits. More about that is here 17 or 15 digits of precision - Excel Tips - MrExcel Publishing
For this particular case if use
=SUMPRODUCT( ($C$1:$C$11<=C4)*($B$1:$B$11=B4))instead of COUNTIFS() it returns correct result.
Thank you, Hans.
It almost sorted the problem. But there were still a few numbers that it couldn't fix. As Joe mentioned, there seems to be some inherent problem in certain odd cases.
The solution that Sergei gave to use sumproduct in place of Countif fixed the problem.