+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : Adding text,Result as Number

  1. 06-01-2010, 02:55 PM #1
    dj0037
    dj0037 is offline
    Registered User
    Join Date
    06-01-2010
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Adding text,Result as Number

    My problem:
    Four cells in a row : A1 - D1 each cell with text.
    where text in cells can be:Single,Duplex,Triplex and Fourplex
    Single=1
    Duplex=2
    Triplex=3
    Fourplex=4

    I need in E1 the result of all cells in numbers with the word "Units" .
    Example:
    text in A1 is "Single" , text in B1 is "Duplex" , C1 and D1 empty then in
    E1 the result should show "3 - Units"

    Michael Said
    Thanks
    Last edited by dj0037; 06-04-2010 at 02:24 PM.
    Reply With QuoteRegister To Reply
  2. 06-01-2010, 03:16 PM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Adding text,Result as Number

    Try

    =SUM(COUNTIF(A1:D1,{"single","duplex","triplex","fourplex"})*{1,2,3,4})
    Reply With QuoteRegister To Reply
  3. 06-01-2010, 03:18 PM #3
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding text,Result as Number

    Try:

    =SUM(IF(A1:D1<>"",LOOKUP(A1:D1,{"duplex","foreplex","single","triplex"},{2,4,1,3})))

    confirmed with CTRL+SHIFT+ENTER not just ENTER
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  4. 06-01-2010, 03:33 PM #4
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Adding text,Result as Number

    Or

    If you need the suffix ....." - Units"

    =IF(AND(A1="",B1="",C1="",D1=""),"",SUM(COUNTIF(A1:D1,{"single","duplex","triplex","fourplex"})*{1,2,3,4}) &" - Units")
    Reply With QuoteRegister To Reply
  5. 06-01-2010, 03:56 PM #5
    dj0037
    dj0037 is offline
    Registered User
    Join Date
    06-01-2010
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Adding text,Result as Number

    Quote Originally Posted by Marcol View Post
    Or

    If you need the suffix ....." - Units"

    =IF(AND(A1="",B1="",C1="",D1=""),"",SUM(COUNTIF(A1:D1,{"single","duplex","triplex","fourplex"})*{1,2,3,4}) &" - Units")
    Did not work

    Quote Originally Posted by NBVC View Post
    Try:

    =SUM(IF(A1:D1<>"",LOOKUP(A1:D1,{"duplex","foreplex","single","triplex"},{2,4,1,3})))

    confirmed with CTRL+SHIFT+ENTER not just ENTER
    Did not work

    Quote Originally Posted by daddylonglegs View Post
    Try

    =SUM(COUNTIF(A1:D1,{"single","duplex","triplex","fourplex"})*{1,2,3,4})
    Worked like a charm,just added &" - Units"
    Thank you all guys for the quick help.
    Reply With QuoteRegister To Reply
  6. 06-01-2010, 04:00 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding text,Result as Number

    Quote:
    Originally Posted by NBVC View Post
    Try:

    =SUM(IF(A1:D1<>"",LOOKUP(A1:D1,{"duplex","foreplex","single","triplex"},{2,4,1,3})))

    confirmed with CTRL+SHIFT+ENTER not just ENTER
    Did not work
    did you confirm with CTRL+SHIFT+ENTER?
    Reply With QuoteRegister To Reply
  7. 06-01-2010, 04:03 PM #7
    dj0037
    dj0037 is offline
    Registered User
    Join Date
    06-01-2010
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Adding text,Result as Number

    Quote Originally Posted by NBVC View Post
    did you confirm with CTRL+SHIFT+ENTER?
    Yes i did

    Thanks
    Reply With QuoteRegister To Reply
  8. 06-01-2010, 04:10 PM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Adding text,Result as Number

    I am not saying that you should not go with the better solution offered by daddylonglegs, but just wanted to show that my solution should work.

    See attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 06-01-2010, 07:30 PM #9
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Adding text,Result as Number

    They all work fine for me,

    I'm out of here, ... .. run for cover , I'll stick to my sookie blanket (VBa) ... .
    Reply With QuoteRegister To Reply
  10. 06-04-2010, 02:22 PM #10
    dj0037
    dj0037 is offline
    Registered User
    Join Date
    06-01-2010
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Adding text,Result as Number

    Quote Originally Posted by NBVC View Post
    I am not saying that you should not go with the better solution offered by daddylonglegs, but just wanted to show that my solution should work.

    See attached.
    Quote Originally Posted by dj0037 View Post
    Yes i did

    Thanks
    You are correct ...it also works ,i might of done something wrong.

    Thanks all for your help
    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