Counting number of times data matches

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Colin - 11 Sep 2007 17:48 GMT

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?

Signature

Thank you,

Colin.

Reply to this Message

krcowen@aol.com - 11 Sep 2007 17:59 GMT

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

> I have two columns of numerical data in range a1:b40. The cells hold values
> from 1 to 9.
[quoted text clipped - 7 lines]
>
> Colin.

Reply to this Message

Peo Sjoblom - 11 Sep 2007 17:59 GMT

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

Signature

Regards,

Peo Sjoblom

>I have two columns of numerical data in range a1:b40. The cells hold values
> from 1 to 9.
[quoted text clipped - 3 lines]
>
> How do I do this?

Reply to this Message

Colin - 12 Sep 2007 08:28 GMT

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.

Signature

Thank you,

Colin.

> If you have numerical entries in all cells you can get around using
>
[quoted text clipped - 17 lines]
> >
> > How do I do this?

Reply to this Message

Mike H - 11 Sep 2007 18:02 GMT

Hi,

Try this array:-=

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

enter with Ctrl+Shift+enter

Mike

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

Reply to this Message

Mike H - 11 Sep 2007 18:02 GMT

Hi,

Try this array:-=

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

enter with Ctrl+Shift+enter

Mike

Reply to this Message