COUNTA Function

[Karen]

I have a formula that counts the active cells in a range
=COUNTA(A1:A10)
Someone inadvertently put a decimal in one of the cells and it counted the
cell when I didn’t want it to. All of the cells will count the cells that
contain a part number, which will always start with a “W†and have a total of
13 characters
Example: W032356412545
Is there a way to set up the COUNTA formula so it only counts the cells that
contain the 13 character part number that starts with the letter W and
eliminate the issue I had if someone inadvertently types in anything else?
Any help would be greatly appreciated
Thanks, Karen

 

[John C]

=SUMPRODUCT(--(LEFT(A1:A100)="w"),--(LEN(A1:A100)=13))

 

[Karen]

Thank you SOOOOOO much! It works great!

=SUMPRODUCT(--(LEFT(A1:A100)="w"),--(LEN(A1:A100)=13))

 

[John C]

Thanks for the feedback, and don't forget to check the little yes box below
to indicate question is answered

 

[Simon Lloyd]

You should explore the use of SUMPRODUCT it's a very powerful function
that acts like an array fomula but doesnt need entering with
Ctrl+Shift+Enter.


--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)

 

[Karen]

Thank you for reminding me John!
Have a great day!

 

[Teethless mama]

Try this:

=COUNTIF(A:A,"W????????????")

 

[ShaneDevenshire]

Hi,

You could use
=COUNTIF(A1:A100,"W*")
but this is not limited to 13 characters, so here is an elegent solution:

=COUNTIF(A1:A100,"W"&REPT("?",12))