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

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


David wrote:
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

"David" skrev i en meddelelse
...
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


Hi David

One way:

=MMULT((LEFT(A1:A5)=TRANSPOSE(LEFT(A1:A5)))+0,(LEF T(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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Thanks vezerid,
much appreciated

"vezerid" wrote:

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


David wrote:
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

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

"Leo Heuser" wrote:

"David" skrev i en meddelelse
...
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


Hi David

One way:

=MMULT((LEFT(A1:A5)=TRANSPOSE(LEFT(A1:A5)))+0,(LEF T(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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

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

Kostis

And please inform us of the comparison results.

Regards,
Kostis


David wrote:
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

"vezerid" skrev i en meddelelse
oups.com...
Wow Leo, that was a good one. Definitely saved in my bag of tricks.

Kostis


Thanks, Kostis :-)

Leo Heuser