Count Occurences problem

[anandmr65]

Hi,

I have the column A with values from 1 to 5 and columns B also haveing
one to five. Now the issue is to count the number of rows where the
value in column B is greater than column A. I tried to use the count if
but could not. Could somebody suggest an easy way out.

Thanks & REgards
Anand

Example
A B
---------
1 2
2 2
1 3
4 5
5 5
3 2
The formula should return 4 in above example

 

[Peo Sjoblom]

I get 3

row 1 =1 B is greater
row2 = 0 equal
row3 = 1 B is greater
row4 = 1 B is greater
row5 = 0 equal
row6 = 0 A is greater


=SUMPRODUCT(--(B1:B6>A1:A6))



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[John James]

The total in your example should be 3, not 4.

This array gives that result, - (P.S. Hold the Ctrl+Shift Keys whilst
pressing enter to have the formula work as an array):

=SUM(--(A1:A6<B1:B6))

 

[Biff]

Hi!

The formula should return 4 in above example

Are you sure about that?

=SUMPRODUCT(--(B1:B6>A1:A6))

Returns 3

Biff

 

[anandmr65]

Hi All,
Thanks a lot all of you for the solution. You are correct with the
answer "3". I was wrong. Your solution works. Sorry for misleading
you.

Thanks again

Regards
Anand