I have a range of cells that contain numbers and numbers with text in the front or in the back of the numbers. I need to count only the cells that have a number with text in the front or the back of it. Ex(P16070 or 16336A). The letter of the alphabet/text and can be from A-Z. How can I use the COUNTIF function to count only those cells that have a number and text in them?
Try
=COUNTA(A2:A10)-COUNT(A2:A10)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
hi CM03486, welcome to the forum. maybe an array like this?
Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
counta-count relies on the numbers being real,if they are text numbers you get 9
as long as all are a mix of letters+numbers or just numbers maybe
=SUMPRODUCT(--ISERROR(A2:A10+1))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks