Countif array formula

D

David

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
 
V

vezerid

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
 
L

Leo Heuser

David said:
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
Click to expand...


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

David

Thanks vezerid,
much appreciated

vezerid said:
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
Click to expand...
 
D

David

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide columns based on row values 2
Vlookup and return sheet name also 2
Excel function - countif 2
Count Number of Unique Cells That Don't Have Certain Characters 0
Making COUNTIF update in real time 4
Excel Countif Function - Calculating Between Two Sets of Values 3
How to Control a formula by a character in adjacent column on same 4
Code works on sheet but not from code-to-sheet 2

Top