+ Reply to Thread
Results 1 to 12 of 12

Counting Mult text or Times in one cell or multiple cells at one tiime?

Hybrid View

  1. 01-29-2013, 04:09 PM #1
    COLTS12OG
    COLTS12OG is offline
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Counting Mult text or Times in one cell or multiple cells at one tiime?

    Times problem.xlsm
    Hello All,
    Having a issue with the above worksheet.
    Would like a formula in Colums J (times) to list 2 or LESS times. and have the cell say "1 time" or "2 times"

    and the range be 2b to e2.

    if there are more then 2 time i would like the cell to just be blank.

    attached i have the worksheet and on the text tab I have how I would like the worksheet to look.
    (highlighted what i would like to see show in the times col.) _I dont want the anything highlighted for the formula..

    Thank you!!
    Reply With QuoteRegister To Reply
  2. 01-29-2013, 06:29 PM #2
    COLTS12OG
    COLTS12OG is offline
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    Is this possible with a formula??
    Reply With QuoteRegister To Reply
  3. 01-29-2013, 09:23 PM #3
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    try this one.

    Times problem (1).xlsm
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  4. 01-30-2013, 03:26 PM #4
    COLTS12OG
    COLTS12OG is offline
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    Awsome, but how do I make all blank cell really blank?
    I see thats the problem...
    Reply With QuoteRegister To Reply
  5. 01-30-2013, 07:39 PM #5
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    You can try the trim function...or find and replace
    =COUNTA(TRIM(I13:I16))

    Find : hit space bar
    Replace All: nothing
    Reply With QuoteRegister To Reply
  6. 01-30-2013, 09:24 PM #6
    COLTS12OG
    COLTS12OG is offline
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    I can see how you did this on this worksheet but when take the formula to anouther works sheet it does not work?
    and when I start to delete a time on the worksheet that you worked on.. it does not show the idication of the time col?

    What am I doing wrong?
    Reply With QuoteRegister To Reply
  7. 01-30-2013, 09:31 PM #7
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    did you change the cell references to the new workbook?
    use the find,replace first then try to delete, just maybe there is still a space being counted, or put the trim function .
    Reply With QuoteRegister To Reply
  8. 01-30-2013, 10:17 PM #8
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    Try

    =IF(SUM(COUNT(B2:E2),(SUM(LEN(B2:E2))-SUM(LEN(SUBSTITUTE(B2:E2,":",""))))/LEN(":"))>2,"",SUM(COUNT(B2:E2),(SUM(LEN(B2:E2))-SUM(LEN(SUBSTITUTE(B2:E2,":",""))))/LEN(":")) & "Times")

    Press ctrl+shift+enter not just enter you'll notice the curly braces around the formula if done properly (Array Formula)
    tell me if this works for you other wise provide another workbook with the error you will encounter thanks.
    Reply With QuoteRegister To Reply
  9. 01-31-2013, 02:51 PM #9
    COLTS12OG
    COLTS12OG is offline
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    Copy of times.xlsm

    I tryed the above code and that did not work..

    I belive it might just be easier if I had a code to tell me every show time in the range of dates?

    Them I could just filert and short the info?

    Do you know of a formula? I attached a bigger worksheet..


    Thank you so much!!!
    Reply With QuoteRegister To Reply
  10. 01-31-2013, 07:31 PM #10
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    I think we are not clear with the requirement

    Would like a formula in Colums J (times) to list 2 or LESS times. and have the cell say "1 time" or "2 times"

    and the range be 2b to e2.

    if there are more then 2 time i would like the cell to just be blank.
    The only row that meets the requirement is Row 175 ->2 times

    in your attachment can you put the real count for atleast 5 rows of data...
    Reply With QuoteRegister To Reply
  11. 01-31-2013, 07:53 PM #11
    COLTS12OG
    COLTS12OG is offline
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    No No,, your right..with the new attachement only row 175 should be marked.. I got it now.. Thank you so much..
    I just need to replace and find...
    what would be a formula if i wanted to could all the times???

    TY TY TY !!!
    Reply With QuoteRegister To Reply
  12. 01-31-2013, 07:56 PM #12
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Counting Mult text or Times in one cell or multiple cells at one tiime?

    get rid of the if ,only the formula itself

    =SUM(COUNT(G2:J2),(SUM(LEN(G2:J2))-SUM(LEN(SUBSTITUTE(G2:J2,":",""))))/LEN(":"))&"Times" ->array entered -ctrl+shift+enter


    Your very much welcome.

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1