count number of rows if value in A is greatr than value in B

mzsaad

New Member
Joined
Aug 24, 2006
Messages
27
hi,
i have 2 columns A and B.

Both have numbers. I need total number of rows in the worksheet in which value of B is greater than value of A.

any help will b appreciated.


regards
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi there, maybe ...

=SUMPRODUCT(--(B1:B300>A1:A300))

Change your ranges to suit, they must be of equal size.

HTH
 
Upvote 0
this might be too simple but why not try in your next blank column having

=IF(A1>B1, 1, 0)

Then copy down

Then at the bottom of that sum have

=SUM(of your new column)

Or do you need more?
 
Upvote 0
Hi mzsaad

Or another way:

=SUM(IF(B1:B100>A1:A100,B1:B100))

confirmed with Ctrl+Shift+Enter (note there is absolutely no advantage using this over Firefytr's SUMPRODUCT - in fact, it will slow down your recalc slightly).

Best regards

Richard
 
Upvote 0
Actually, I don't know if it would even do that Richard. Array's are lightning fast and I've generally seen that it's the SUMPRODUCT who is the slower culprit.. The main advantage I see there is that most beginner-intermediate Excel user's forget down the road that the formula is a CSE and get the #VALUE! error...
 
Upvote 0
I have found at work that SUMPRODUCTs take up a lot less memory in the saved file (ie it is appreciably smaller in terms of Kb/Mb when using SUMPRODUCTs than Array formulas) assuming of course that you have a lot of Sumproduct or array formulas. But I have to admit, it is straightforward prejudice that the SUMPRODUCTs will calc faster!

I do agree the array formulas are a git if you 'forget' to CSE them...

Richard
 
Upvote 0
Hmm..never thought about it.....

So which is faster...Arrays or SUMPRODUCT?

Aladin, get out your Excel tool... :)
 
Upvote 0
I've seen this thrown back and forth many times, especially between Aladin, Tushar and some others. I do not have FastExcel or any real means of accurately testing my application calculation speeds. My comment was only from my experience as to where my deductions led me.

Just wanted to post that disclosure. :lol:
 
Upvote 0
I've seen this thrown back and forth many times, especially between Aladin, Tushar and some others. I do not have FastExcel or any real means of accurately testing my application calculation speeds. My comment was only from my experience as to where my deductions led me.

Just wanted to post that disclosure. :lol:

Not just me, but also Charles Williams published performance comparisons which show the SumProduct formulas to be faster when both types of formulas apply.
 
Upvote 0
Re: count number of rows if value in A is greatr than value

hi,
i have 2 columns A and B.

Both have numbers. I need total number of rows in the worksheet in which value of B is greater than value of A.

any help will b appreciated.


regards

I believe the empty cells issue has not been addressed by suggestions already forwarded...

One of:

=SUMPRODUCT(--ISNUMBER($A$2:$A$400),--ISNUMBER($B$2:$B$4),--($B$2:$B$4 > $A$2:$A$400)

{=SUM(IF(ISNUMBER($A$2:$A$400),IF(ISNUMBER($B$2:$B$400),($B$2:$B$400 > $A$2:$A$400)+0)))}
 
Upvote 0

Forum statistics

Threads
1,223,430
Messages
6,172,065
Members
452,444
Latest member
ShaImran193

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top