+ Reply to Thread
Results 1 to 3 of 3

SumIf if First 10 Characters match cell

  1. 02-25-2016, 08:21 AM #1
    WaylettChris
    WaylettChris is offline
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Exclamation SumIf if First 10 Characters match cell

    Hi All,

    I have a conundrum for you all...

    I have a product code in Column A, a quantity of that product in Column C.

    Some product codes start with the same first 10 letters, but might have a suffix at the end denoting a slight variation i.e. there might be:

    A-0002-GEN, A-0002-GEN-SSO and A-0002-GEN-HAR.

    What I need is, a formula which I can enter in Cell D1 which will check column A for codes with the first 10 letters all the same as Cell A1 and then do a sumif of the corresponding values in Column C.

    I hope this all makes sense...I have inserted an example to show you what I mean.

    HELP!!




    Chris
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-25-2016, 08:33 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: SumIf if First 10 Characters match cell

    Perhaps

    Try
    D2=SUMPRODUCT((LEFT($A$2:$A$23,10)=A2)*($C$2:$C$23))
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 02-25-2016, 08:42 AM #3
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: SumIf if First 10 Characters match cell

    In D2-
    Please Login or Register  to view this content.
    copied down...

    CODE FREE QTY FORMULA USED RESULT
    A-0001-GEN 125 =SUMIF(A:A,"*"&LEFT(A2,10)&"*",C:C) 125
    A-0002-GEN 972 =SUMIF(A:A,"*"&LEFT(A3,10)&"*",C:C) 1055
    A-0002-GEN-NONCARB 83 =SUMIF(A:A,"*"&LEFT(A4,10)&"*",C:C) 1055
    A-0003-GEN 540 =SUMIF(A:A,"*"&LEFT(A5,10)&"*",C:C) 540
    A-0004-GEN 14 =SUMIF(A:A,"*"&LEFT(A6,10)&"*",C:C) 14
    A-0005-GEN 50 =SUMIF(A:A,"*"&LEFT(A7,10)&"*",C:C) 50
    A-0008-GEN 272 =SUMIF(A:A,"*"&LEFT(A8,10)&"*",C:C) 272
    A-0009-GEN-SSO 33 =SUMIF(A:A,"*"&LEFT(A9,10)&"*",C:C) 33
    A-0010-SLT 20 =SUMIF(A:A,"*"&LEFT(A10,10)&"*",C:C) 34
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh
    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)

Similar Threads

  1. match on last 4 characters of a cell value to data on another spreadsheet
    By mklindquist0815 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2016, 01:19 PM
  2. Using Sumif() to find cell with match values in two columns
    By my2108 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2015, 03:14 AM
  3. Match comparing characters not the cell value
    By ABBOV in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-02-2015, 10:41 AM
  4. Formula to MATCH Sheet name to a particular cell and if it matches do a SUMIF on the work
    By allygater in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-07-2015, 09:05 PM
  5. SUMIF, Index and Match Formula. Return Cell value
    By FLani in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2014, 02:43 AM
  6. 2010 - Need to match data and return specific cell value - SUMIF? MATCH? INDEX? HELP!
    By AJev in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 02:10 PM
  7. Match 2 Lists Depending on a the first Characters of the cell-which will be variable
    By denise001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2011, 02:46 AM

Tags for this Thread

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