Countif array formula
 

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

Countif array formula
 

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

Countif array formula
 

"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.

Countif array formula
 

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

Countif array formula
 

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.

Countif array formula
 

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

Kostis

Countif array formula
 

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

Countif array formula
 

"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