COUNTIF Anomaly

The formula in column D is supposed to put the dates in columns A:B in ascending order, eliminating duplicates (though there are none in this example).

	A	B	C	D	E
1	Begin	End		Ordered
2	09/23/2016 10:41:42	09/23/2016 10:43:42		09/23/2016 10:41:42	D2: =SMALL($A$2:$B$7, COUNTIF($A$2:$B$7, "<=" & D1) + 1)
3	09/24/2016 08:41:57	09/24/2016 11:41:45		09/23/2016 10:43:42
4	09/24/2016 09:43:25	09/24/2016 15:16:48		09/24/2016 08:41:57
5	09/24/2016 09:48:11	09/24/2016 11:25:32		09/24/2016 08:41:57
6	09/24/2016 09:51:54	09/24/2016 11:24:54		09/24/2016 08:41:57
7	09/24/2016 11:45:00	09/24/2016 12:54:49		09/24/2016 08:41:57

Why does the formula start repeating the same result in D5?

It's the COUNTIF part that's returning the wrong result.

Cross-posted at https://www.mrexcel.com/forum/excel-...ml#post4831646

Hey shg, the problem is the specific value 9/24/2016 8:41:57 AM. Change the last digit "7" to "8" and your formula works.
The serial equivalent (ignoring the whole number portion) is .3624652777. Some sort of rounding error, I guess. COUNTIF doesn't recognize A3=D4!
The equivalent fraction would be 31317/86400.
31317 is NOT a prime number. Factors{3,11,13,73}

or change it to a 6...
"24/09/2016 8:41:57" seems to be some sort of magic time for breaking countifs.

Maybe this?

Formula:

Please Login or Register  to view this content.

v	A	B	C	D
2	9/23/2016 10:41:42	9/23/2016 10:43:42		9/23/2016 10:41:42
3	9/24/2016 8:41:57	9/24/2016 11:41:45		9/23/2016 10:43:42
4	9/24/2016 9:43:25	9/24/2016 15:16:48		9/24/2016 8:41:57
5	9/24/2016 9:48:11	9/24/2016 11:25:32		9/24/2016 9:48:11
6	9/24/2016 9:51:54	9/24/2016 11:24:54		9/24/2016 9:51:54
7	9/24/2016 11:45:00	9/24/2016 12:54:49		9/24/2016 11:24:54

try rounding all your date/times to something like 8 decimal places and then rounding the formula to round to the same number of decimal places (eg in this case 8).
maybe something like

Please Login or Register  to view this content.

then you should get around the odd rounding of that time.

Although COUNTIF didn't recognize them as EQUAL, the formula =(A3=D4) yields TRUE, so this array formula WORKS!:

=SMALL($A$2:$B$7, SUM(--($A$2:$B$7<= N(D1))) + 1)

I'm guessing this is a 32-bit / 64-bit thing where COUNTIF didn't get updated correctly. I notice everyone who's responded is using Excel 2010 or later.
We need somebody w 32-bit version (2007 or earlier) to check this.

Thank you all for responding.

The problem also goes away if I first round the numbers to the nearest second, but changing data to make formulas work is not good.

I verified that the SMALL function does indeed return members of the input set of numbers; it doesn't alter them slightly to fool the COUNTIF function. It seems like a very strange failure mode for COUNTIF, considering that a test for "<=" is very simple.

I ended up using leelnich's suggestion:

=SMALL($A$2:$B$7, SUM(--($A$2:$B$7 <= N(D1))) + 1)

Thank you all again.