COUNTA Function
 

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

COUNTA Function
 

=SUMPRODUCT(--(LEFT(A1:A100)="w"),--(LEN(A1:A100)=13))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Karen" wrote:

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

COUNTA Function
 

Thank you SOOOOOO much! It works great!

"John C" wrote:

=SUMPRODUCT(--(LEFT(A1:A100)="w"),--(LEN(A1:A100)=13))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Karen" wrote:

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

COUNTA Function
 

Thanks for the feedback, and don't forget to check the little yes box below
to indicate question is answered :)
--
** John C **


"Karen" wrote:

Thank you SOOOOOO much! It works great!

"John C" wrote:

=SUMPRODUCT(--(LEFT(A1:A100)="w"),--(LEN(A1:A100)=13))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Karen" wrote:

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

COUNTA Function
 

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27543

COUNTA Function
 

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

"John C" wrote:

Thanks for the feedback, and don't forget to check the little yes box below
to indicate question is answered :)
--
** John C **


"Karen" wrote:

Thank you SOOOOOO much! It works great!

"John C" wrote:

=SUMPRODUCT(--(LEFT(A1:A100)="w"),--(LEN(A1:A100)=13))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Karen" wrote:

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

COUNTA Function
 

Try this:

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



"Karen" wrote:

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

COUNTA Function
 

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

--
Thanks,
Shane Devenshire


"Karen" wrote:

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