Count column difference

[wizzy]

Hi
Using MSExcel 97.

I have two columns of data
e.g. A1: A4, containing values 5,10, 3, 6
B1:B4, containing values 3, 8, 7, 4

I wish to perform a count (e.g. in C5) of the number of rows where the
value in column A exceeds the respective value in column B (in this
case count = 3, as A1>B1, A2>B2, and A4>B4).

Just cannot get my formula right. Tried using an array (but difficult
when comparing the difference between two columns), and COUNT.

Thanks in advance for any suggestions.

Wizzy

 

[Paul]

Hi
Using MSExcel 97.

I have two columns of data
e.g. A1: A4, containing values 5,10, 3, 6
B1:B4, containing values 3, 8, 7, 4

I wish to perform a count (e.g. in C5) of the number of rows where the
value in column A exceeds the respective value in column B (in this
case count = 3, as A1>B1, A2>B2, and A4>B4).

Just cannot get my formula right. Tried using an array (but difficult
when comparing the difference between two columns), and COUNT.

Thanks in advance for any suggestions.

Wizzy

One way:
=SUMPRODUCT((A1:A4>B1:B4)*1)
No need to array-enter.

 

[wizzy]

Paul
Top notch. Thank you very much.