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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=534008

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:B6A1: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


"anandmr65" wrote
in message ...

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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile:
http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=534008

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))


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=534008

Hi!

The formula should return 4 in above example

Are you sure about that?

=SUMPRODUCT(--(B1:B6A1:A6))

Returns 3

Biff

"anandmr65" wrote
in message ...

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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile:
http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=534008

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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=534008