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

##### New Member
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi there, maybe ...

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

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

HTH

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?

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

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

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

Hmm..never thought about it.....

So which is faster...Arrays or SUMPRODUCT?

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.

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.

Not just me, but also Charles Williams published performance comparisons which show the SumProduct formulas to be faster when both types of formulas apply.

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

Replies
3
Views
228
Replies
2
Views
105
Replies
5
Views
156
Replies
4
Views
173
Replies
1
Views
386

1,207,390
Messages
6,078,204
Members
446,321
Latest member
thecachingyeti

### 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?

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