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.
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.