Count cells in a range greater than cells in another range

[D.Lovell]

Hi,

I have been battling with this for a while now, there must be a relatively simple way of doing it, seems like an obvious thing that one might want to do in excel.

Imaging you have a number of different values in column 'A' going down to say, line 100.

We then also have a number of values in column 'B' also going down to line 100. The values in columns 'A' & 'B' are different.

I want to create a formular in the cell at the bottom which states how many cells in column B are greater than the adjacent cell in column A. Due to the layout of the sheet I want to acheive this without having to create an extra column. I would also prefer to avoid using a huge chain of the COUNTIF funtion for each line seperately.

I have heard that this may be acheivable using the SUMPRODUCT function, although so far I have not been able to figure out how. I also suspect it could be acheived by writting a custom function in VBA that will use the COUNTIF comand but inside a loop. Again I have not worked out exactly how to do it.

Can anyone suggest a solution for me here please?

Hopefully I have explained my intentions clearly, if not please advise and I will elaborate further.

Many thanks

[dredwolf]

How about:
=SUMPRODUCT(--($B$1:$B$100>$A$1:$A$100))

Hope this helps

[Pete_UK]

Try this formula:

=SUMPRODUCT(--(A1:A100>B1:B100))

Hope this helps.

Pete

[ConneXionLost]

Try this array formula:

Please Login or Register  to view this content.

Confirm as an array with Ctrl-Shift-Enter.

[rcm]

the VBA should be something like this:
it will write the requested value at the bottom of the B column (2 rows after the last entry)

Please Login or Register  to view this content.

[D.Lovell]

Thanks guys that is extreemly helpful and I have done it at last.

Seems there are a few ways of acheiving it then

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"