+ Reply to Thread
Results 1 to 6 of 6

Sumif based on first few characters of a column

  1. #1
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172

    Thumbs up Sumif based on first few characters of a column

    Can you do a sumif based on the first 4 characters of a column? For example:

    adcd123 100
    adcd290 200
    adcd550 600
    aklm990 300
    akht880 500
    adcd127 100

    So sumif for adcd is 1000. I'm trying to do this without creating another column for just the first 4 characters and then sumif-ing off that column. Any ideas?

  2. #2
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Sumif based on first few characters of a column

    =SUMIF(A:A,"adcd*",B:B)

    a column is text and b column is numbers
    Appreciate the help? CLICK *

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sumif based on first few characters of a column

    In spite of your request I used a helpcolumn and an pivot table.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172

    Re: Sumif based on first few characters of a column

    That works AZ, but I don't want to type every single one of the conditions in column A, I want to just use the "LEFT(A1,4)" and you can't use an astrik with that function.

    Thanks oeldere, but again, I don't want to insert another column in the spreadsheet, which I'm currently doing to make this work.

  5. #5
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172

    Re: Sumif based on first few characters of a column

    Actually AZ, I stand corrected, I used =Left(A1,4)&"*", and it worked. Thanks.

  6. #6
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Sumif based on first few characters of a column

    =SUMPRODUCT((LEFT(A1:A6,4)="adcd")*B1:B6)
    or

    suppose that criterie (in this exampple adcd in d1 cell)
    =SUMPRODUCT((LEFT(A1:A6,4)=D1)*B1:B6)

+ 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