| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counta function | Excel Worksheet Functions | |||
| Output of the =IF(COUNTA) Function | Excel Worksheet Functions | |||
| Using the COUNTA function | Excel Discussion (Misc queries) | |||
| Counta function question | Excel Worksheet Functions | |||
| COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Hybrid Mode
