COUNTIF array function not working

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?

Originally Posted by dredwolf

Hrrm..did same to me, including the sum if...sumproduct returned the right number as well..

I do have 1 question, why are you array entering the countif...you get the same number without it (still WRONG, but the same result ) ?

The countif function won't work on the two columns unless its input as an array function.

6 that the CountIf formula cell shows does not answer OP's question since OP qualifies the result from the Sum array formula as correct. Rather, the CountIf formula calculates how many cells in the A range is smaller ( < ) than each value in the B range, which results in an array whose size is equal to that the criteria range. Since the result is an array, the formula cell displays the first item of the result array (it cannot do otherwise). Hence 6.

Thanks all!!!