MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula Help - probably an Array Formula


Posted by JAF on September 12, 2000 8:10 AM

I have a column of data which contains product codes (NLA001, NLA004, DHA002, DHA005, DHA007 etc).

What I need is a formula (which will ultimately form part of a larger Array Formula) to count the number of instances where the 3 leftmost characters in Column A are NLA.

I've been mucking about with this for ages with various combinations of LEN and COUNTIF, but I can't get it to work. I'm sure it's something really obvious that I'm doing wrong, but the solution eludes me!

Any help greatly appreciated.


JAF


Posted by jaf on September 12, 0100 8:19 AM

Solution Found

Yet again, I've managed to work out what I need to do just minutes after posting my request, despite having tried without success for ages beforehand!

If you're interested, the solution was...
=SUM((LEFT(A1:A1000,3)="NLA")*1)


JAF