+ Reply to Thread
Results 1 to 11 of 11

Count number after text

  1. #1
    Registered User
    Join Date
    10-31-2017
    Location
    San Bruno, California
    MS-Off Ver
    2007
    Posts
    7

    Count number after text

    So I'm relatively green when it comes to excel. I wan't to set up a way to distinguish specific sets of numbers by containing text in the same cell, and create a formula that'll count the numbers based on the accompanying text. for example: "Blanket:2" in one cell, and "Square: 2" in another, while having a formula to count the numbers individually. I do know that I could set up two columns or rows and have them count based on the cells, but will not work on the project that I am making.
    Last edited by JohnTitor; 10-31-2017 at 01:00 PM.

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Count number after text

    Hi John, welcome to the forum. A small sample workbook (NOT a picture) would really help us to solve your problem quickly and accurately.
    Please remove any sensitive or extraneous info.
    Try to preserve the original layout so our solutions fit your workbook (optional).
    Provide “realistic” data. Include any variations the code or formula must address.
    IMPORTANT: Simulate some results to demonstrate what you want.

    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now click Browse, find your file, then click Upload. Simple!
    Last edited by leelnich; 10-31-2017 at 12:22 PM.
    Clicking the Add Reputation star below any helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion. (Thread Tools above Post # 1) Thanks!-Lee

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,238

    Re: Count number after text

    just a thought, if you have a column with Blanket:2 and Square: 2 you could always use text to columns with a colon as the delimiter and separate the values into another column then simply add them.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    10-31-2017
    Location
    San Bruno, California
    MS-Off Ver
    2007
    Posts
    7

    Re: Count number after text

    Oh, sorry! Here's the workbook:
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-31-2017
    Location
    San Bruno, California
    MS-Off Ver
    2007
    Posts
    7

    Re: Count number after text

    Quote Originally Posted by Sambo kid View Post
    just a thought, if you have a column with Blanket:2 and Square: 2 you could always use text to columns with a colon as the delimiter and separate the values into another column then simply add them.
    Thank you for your reply, though I'm not sure if delimiters can help me in my case!

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,238

    Re: Count number after text

    is that how the data will be consistently shown? PTO: and a number or U.PTO: and a number? Nothing else?

  7. #7
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Count number after text

    Try this ARRAY FORMULA* in B41,copied across:
    Please Login or Register  to view this content.
    ...and similarly, in B44, copied across:
    Please Login or Register  to view this content.
    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.
    Attached Files Attached Files
    Last edited by leelnich; 10-31-2017 at 12:50 PM.

  8. #8
    Registered User
    Join Date
    10-31-2017
    Location
    San Bruno, California
    MS-Off Ver
    2007
    Posts
    7

    Re: Count number after text

    Yes, it will be consistently inputed like that

  9. #9
    Registered User
    Join Date
    10-31-2017
    Location
    San Bruno, California
    MS-Off Ver
    2007
    Posts
    7

    Re: Count number after text

    This helps, a ton! thank you so much +rep!

  10. #10
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Count number after text

    You're welcome, thanks for the rep.

    BTW, If you ever have issues with these array formulas, make sure they're array-entered. Double-click on the cell to enter Edit Mode and press CTRL+SHIFT+ENTER (all at once). You should see curly brackets {} embrace the formula text, signifying array status.
    Last edited by leelnich; 10-31-2017 at 01:12 PM.

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count number after text

    Here is a shorter version:
    In B41
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For B44
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Array formulas
    ***Array formula
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    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.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count number of lines based on text and number criteria, without duplicates
    By HVCompleto in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2017, 07:17 AM
  2. Replies: 17
    Last Post: 04-11-2016, 11:14 PM
  3. Count Text and Number
    By sriku in forum Excel General
    Replies: 2
    Last Post: 05-20-2014, 11:44 AM
  4. [SOLVED] Formula to count the number of spaces before text/number is written in a cell.
    By kmis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-12-2013, 05:05 PM
  5. How to Count number with text
    By summer2010 in forum Excel General
    Replies: 6
    Last Post: 06-10-2011, 11:18 AM
  6. Replies: 2
    Last Post: 08-16-2010, 10:44 AM
  7. how do I count the number of times text in column A matches text i
    By Sheila in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-16-2005, 06:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1