Countif array formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 17 Oct 2006 10:06 GMT

Greetings,
I have range A1:A5 i need to test for repeating cell contents
Array formula =countif($A$1:$A$5,A1:A5) works fine and for cell contents:
A,A,B,C,D returns: 2,2,1,1,1
Now I wish to test for repeating leftmost characters, ie for cell contents:
AF,DT,PQ,AS,AD i need to return: 3,1,1,3,3
I've tried: =countif(LEFT($A$1:$A$5),LEFT(A1:A5)) entered as array formula
but returns an error
Please advise
Thanks

Reply to this Message

vezerid - 17 Oct 2006 11:18 GMT

David,

COUNTIF only accepts range refs in the 1st argument and not virtual
arrays. You will need to use SUMPRODUCT for this:

=SUMPRODUCT(--(LEFT($A$1:$A$5)=LEFT(A1)))

No array-entering now, just copy downwards.

Does this help?
Kostis Vezerides

> Greetings,
> I have range A1:A5 i need to test for repeating cell contents
[quoted text clipped - 6 lines]
> Please advise
> Thanks

Reply to this Message

David - 17 Oct 2006 13:05 GMT

Thanks vezerid,
much appreciated

> David,
>
[quoted text clipped - 18 lines]
> > Please advise
> > Thanks

Reply to this Message

Leo Heuser - 17 Oct 2006 11:20 GMT

> Greetings,
> I have range A1:A5 i need to test for repeating cell contents
[quoted text clipped - 7 lines]
> Please advise
> Thanks

Hi David

One way:

=MMULT((LEFT(A1:A5)=TRANSPOSE(LEFT(A1:A5)))+0,(LEFT(A1:A5)=LEFT(A1:A5))+0)

To be entered with <Shift><Ctrl><Enter>

COUNTIF() can only be used on a range, not on an array and
LEFT($A$1:$A$5) is an array.

Signature

Best regards
Leo Heuser

Followup to newsgroup only please.

Reply to this Message

David - 17 Oct 2006 13:09 GMT

Thanks Leo,
I look forward to trying both solutions tonight. My finished app will run
this many 1000s of times. - It'll be interesting to see how this compares to
the other solution on performance
Thanks again

> > Greetings,
> > I have range A1:A5 i need to test for repeating cell contents
[quoted text clipped - 18 lines]
> COUNTIF() can only be used on a range, not on an array and
> LEFT($A$1:$A$5) is an array.

Reply to this Message

vezerid - 17 Oct 2006 13:34 GMT

And please inform us of the comparison results.

Regards,
Kostis

> Thanks Leo,
> I look forward to trying both solutions tonight. My finished app will run
> this many 1000s of times. - It'll be interesting to see how this compares to
> the other solution on performance
> Thanks again

Reply to this Message

vezerid - 17 Oct 2006 13:28 GMT

Wow Leo, that was a good one. Definitely saved in my bag of tricks.

Kostis

Reply to this Message

Leo Heuser - 17 Oct 2006 16:29 GMT

> Wow Leo, that was a good one. Definitely saved in my bag of tricks.
>
> Kostis

Thanks, Kostis :-)

Leo Heuser

Reply to this Message