Countif array formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Excel Need Countifs Formula Help 0
Excel function - countif 2
Making COUNTIF update in real time 4
Vlookup and return sheet name also 2
Excel formula, sum the value cell and if empty cell left blank 0
Help with function 2
Function to Return a Date Value Q 4
Using countif on non-consecutive cells 5

Back
Top