LinkBack URL
About LinkBacks
Hello Friends
In the column A need to count only 6 digit numbers by formula, here duplicate will not come for 6 digit number.
Hello Friends
In the column A need to count only 6 digit numbers by formula, here duplicate will not come for 6 digit number.
Sekar
Try
=SUM(--(LEN(A1:A73)=6)) Ctl Shift Enter
with your example
try =SUM(COUNT(A1:A73,ISNUMBER(A1:A73))) array entered
or
simply COUNT()
Last edited by sandy666; 06-02-2017 at 10:02 AM.
or
=SUMPRODUCT(--(LEN(A1:A200)=6))
(don't need CNTRL SHFT ENTER)
taking into account that the 6 characters might not be a number (thanks Sandy)
=SUMPRODUCT(--(LEN(A1:A200)=6),--(ISNUMBER(A1:A200)))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
With your data, you can just use...
=COUNT(A1:A73)
By default, COUNT function ignores texts.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
I re-read post#1 and agree with ChemistB in post #4 : no array, 6 digits, numbers only
Last edited by sandy666; 06-02-2017 at 10:07 AM. Reason: typo
I'll second that, ChemistB's formula is the more robust method (when there could be numeric value with more/less than 6 char).I re-read post#1 and agree with ChemistB in post #4
Another way to write the same formula. "*" multiplication serves to force result into number.
=SUMPRODUCT(ISNUMBER(A1:A73)*(LEN(A1:A73)=6))
Hello 63falcondude, sandy, ChemistB, CK76
Thanks for all of your formulas. All formulas working well, my datas are being added and even if i changed 73 to 200 it is working fine.
Simple count formula cannot be used, since it is simply counting irrespective of digits (here it is 6).
thanks again.
So example was inconcistent
but if any of solution works for you that's good.
You are welcome and thanks for the feedback
True, some people prefer using multiplication instead of "--" Either will force a number. I prefer using commas because, in the cases where you are summing a column (not this case), text will result in an error with multiplication.Originally Posted by CK76
"
Glad we could help. Thanks for the rep!
Last edited by 63falcondude; 06-02-2017 at 10:52 AM.
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
For normal conversational replies, try using the QUICK REPLY box below.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks