+ Reply to Thread
Results 1 to 3 of 3

Array Formula with LEFT and FIND

  1. #1
    Registered User
    Join Date
    08-15-2008
    Location
    Mass
    MS-Off Ver
    2016, 64- Bit
    Posts
    28

    Array Formula with LEFT and FIND

    I have cells A1, B1, and C1. The cells contain the values "100-BAS", "150-BAS", and"250-CHECK" respectively. I want to be able to sum the numberic porstion of the 3 strings. Ican make a mega forumula and add the contents of all three but using
    "=VALUE(LEFT(A1,FIND("=",A1)-1))". How can I do this by utilizing an array?

    If I do =SUM(VALUE(LEFT(A1:C1,FIND("=",A1:C1)-1))) and press CTRL+SHIFT+ENTER I get a #VALUE! error.

    Thanks in advance!

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: Array Formula with LEFT and FIND

    Hi,

    It's a small typo in your formula. = should be -

    Please Login or Register  to view this content.
    You could also do it with SUMPRODUCT():
    Please Login or Register  to view this content.
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Array Formula with LEFT and FIND

    How about

    =SUMPRODUCT(--LEFT(A1:C1,3))

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1