Counting number of times data matches

[Guest]

I have two columns of numerical data in range a1:b40. The cells hold values
from 1 to 9.
I want to count the number of times 'column a' data is less than or equal to
'column b' data.

How do I do this?

 

[krcowen]

Colin

If you want to do it with a single array formula, try:

=SUM(--(A1:A40<B1:B40))

entered as an array (shift-ctrl-enter)

Good luck.

Ken
Norfolk, Va

 

[Peo Sjoblom]

If you have numerical entries in all cells you can get around using

=SUMPRODUCT(--(A1:A40<=B1:B40))


but it will count blank cells and text


=SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40)))


will take care of blank cells


=SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40)),--(ISNUMBER(B1:B40)))


will take care of blank cells and text

 

[Guest]

Hi,

Try this array:-=

=COUNT(IF(A1:A40<=B1:B20,A1:A40,FALSE))

enter with Ctrl+Shift+enter

Mike

 

[Guest]

Hi,

Try this array:-=

=COUNT(IF(A1:A40<=B1:B40,A1:A40,FALSE))

enter with Ctrl+Shift+enter

Mike

 

[Guest]

Hello,

Thank you all for your suggestions.
Without realising it, I did need to avoid counting cells with text in it.
The arrays and the Count formula both worked fine.
--
Thank you,

Colin.

If you have numerical entries in all cells you can get around using

=SUMPRODUCT(--(A1:A40<=B1:B40))


but it will count blank cells and text


=SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40)))


will take care of blank cells


=SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40)),--(ISNUMBER(B1:B40)))


will take care of blank cells and text