#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 447
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 447
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 447
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counta function Help Excel Worksheet Functions 3 July 2nd 08 07:01 PM
Output of the =IF(COUNTA) Function Excel Helps Excel Worksheet Functions 2 January 21st 08 01:30 PM
Using the COUNTA function JL1976 Excel Discussion (Misc queries) 1 October 26th 07 01:28 AM
Counta function question Karen McKenzie Excel Worksheet Functions 10 October 5th 06 12:08 PM
COUNTA Function not working =COUNTA(C3:C69,"NH") MikeinNH Excel Worksheet Functions 2 November 8th 04 02:19 AM


All times are GMT +1. The time now is 10:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"