Counting number of times data matches
 

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?

--
Thank you,

Colin.

Counting number of times data matches
 

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


On Sep 11, 12:48 pm, Colin wrote:
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?

--
Thank you,

Colin.

Counting number of times data matches
 

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



--
Regards,

Peo Sjoblom



"Colin" wrote in message
...
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?

--
Thank you,

Colin.

Counting number of times data matches
 

Hi,

Try this array:-=

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

enter with Ctrl+Shift+enter

Mike

"Colin" wrote:

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?

--
Thank you,

Colin.

Counting number of times data matches
 

Hi,

Try this array:-=

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

enter with Ctrl+Shift+enter

Mike

"Colin" wrote:

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?

--
Thank you,

Colin.

Counting number of times data matches
 

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.


"Peo Sjoblom" wrote:

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



--
Regards,

Peo Sjoblom



"Colin" wrote in message
...
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?

--
Thank you,

Colin.