Help on comparing two columns and counting the occurences

M

mario

Consider my following excel sheet

A |B |C |D
----------------------------------------------
1 |Student |Teacher1_Rank |Teacher2_Rank
2 |Mark |A |B
3 |Jeena |C |A
4 |Marie |D |D


What will be the formual to count

1. The number of students who received same ranks from
two teachers

2. How many students received higher rank from Teacher2
than from Teacher1

3. How many students received lower rank from Teacher2
than from Teacher2


Please help
 
M

mario

Hi Jason,

Thanks for your prompt reply. I tried the formulas and
got the following results, it didnt work

1. 0
2.#VALUE!
3.#VALUE!

Can you please explain what does "N" and "CODE" mean. I
couldnt find about "N" and "CODE" from help (F1).

thanks
 
M

Mike

A B C
1 Student Teacher1_Rank Teacher2_Rank
2 Mark A B
3 Jeena C A
4 Marie D D

1. =SUMPRODUCT((B2:B4=C2:C4)*1)
2. =SUMPRODUCT((B2:B4>C2:C4)*1)
3. =SUMPRODUCT((B2:B4<C2:C4)*1)
 
Top