Hi,

I apologise if this was discussed in a previous thread.

I'm currently facilitating an Excel course and while covering array functions I noticed that my COUNTIF function wasn't giving the correct solution for the following data set:

2,980 793
838 756
690 766
865 843
726 480
939 643
559 440
678 761
438 746
3,838 633
1,580 745
1,229 687
1,224 742
367 753
1,126 607
970 1,047
3,014 745

The formula I used is:
{=COUNTIF(A2:A18,"<"&B2:B18)}
Which output "6", where the answer should be "5".

I also used SUM(IF()) which gave me the right answer.
{=SUM(IF(A2:A18<B2:B18,1,0))}

I suspect the problem may have to do with duplicates or something peculiar about this particular data set. I promised to find out the root of the problem to the course participants. Can anyone help me with an explanation or tell me what I'm doing wrong?