value is greater in a column than corresponsing value in ano

[mzsaad]

here is the question:

i need to know how many rows exist in which value of A is greater than corresponding value in B.

for example, i have two columns. A and B. Both have numbers:

A|B
----
1|3
2|4
5|4
2|4
2|4

for this example, i m looking for the answer 1 here (as there is only one column in which value of A is greater than corresponding value of B (3rd row)).

thanks in advance.

regards

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(A1:A10>B1:B10))

 

[mzsaad]

thnx for the reply.

i think ur formula is giving me sums of numbers in A whose value is greater than corresponding number in B.

what i need is number of rows in which number in A is greater than corresponding number in B.

regards

 

[Wismer]

Hi,

I have tested the same formula and it works fine for me. The answer after applying the formula based on the example you posted showed as 1

Peace
Wismer

 

[Oaktree]

Fairwinds' formula does what you're asking

To return the sum of the values, you'd use =SUMPRODUCT(--(A1:A10>B1:B10),A1:A10)

 

[mzsaad]

yup my bad.

i was referencing to wrong worksheet within my excel file.

thanks

regards