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