COUNTIF value in column A is <, =, or < corresponding value in same row, column B

With regards to an annual test, I have the most recent test scores in column A and the previous scores in column B. I want to count the number of individuals who's scores have improved, the number who have stayed the same, and the number who have worsened.

I have been using the COUNTIF function as follows:

Attempt 1:
=COUNTIF(A2:A100,">"&B2:B100)

This gives a result of 0 despite many occurrences of A values being greater than their corresponding B values.

Attempt 2:
=COUNTIF(A2:A100,">"&B2)
This seems to give me the number of A values that are greater than B2 specifically, as if I had written it $B$2, instead of the B value in the same row.

I thought perhaps maybe the gaps in Data were causing my issues (i.e. I don't have scores for every individual on the list as some of them have not taken the test yet, or did not take the test last year) So I tried entering all vacant scores as 0 but this did not solve any issues.

If anyone could provide some insight into how to properly return the data I am looking for it would be much appreciated!! Thank you!

Use these formulas entered as array formulas. That is, instead of just typing them in and hitting ENTER, hit CTRL+SHIFT+ENTER.

=SUM(IF($A$2:$A$100>$B$2:$B$100,1,0))
=SUM(IF($A$2:$A$100=$B$2:$B$100,1,0))
=SUM(IF($A$2:$A$100<$B$2:$B$100,1,0))

You will know the array is active when you see curly braces { } appear around your formula. You cannot type in the braces, but you will see them in the formula bar.

Or, normal formulae:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.