+ Reply to Thread
Results 1 to 8 of 8

How to Sum cells that begin with the same sequence of numbers?

  1. #1
    Registered User
    Join Date
    02-04-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    How to Sum cells that begin with the same sequence of numbers?

    Hi,

    I'm currently trying to figure out a way to sum a column of cells that begin with a common sequence of numbers. Is there a formula I can put in to automatically congregate all the cells in the left hand column based on their first 3 digits and sum their corresponding totals in the right hand column?

    For example, something like =if(columnA="850...",sum(columnB),"")??

    Thanks in advance.

    Status DENIED

    Row Labels Count of Dealer
    3258 1
    84309 1
    84532 1
    85029 1
    85048 1
    85064 1
    85091 1
    85436 1
    85437 1
    85511 1
    85512 1
    85540 1
    86436 1
    93129 1
    93246 1
    93378 1
    93493 1
    93567 1
    93710 1
    94086 1
    94180 4
    Last edited by kpruitt44; 02-04-2014 at 11:55 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to Sum cells that begin with the same sequence of numbers?

    Try SUMIF with wildcard, such as:
    =SUMIF(COL_A,850&"*",COL_B)
    Quang PT

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to Sum cells that begin with the same sequence of numbers?

    Hi,

    waiting for better solutions


    Please Login or Register  to view this content.
    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  4. #4
    Registered User
    Join Date
    02-04-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to Sum cells that begin with the same sequence of numbers?

    Thanks for your suggestions.

    However, I tried both and neither seems to be giving the the results I need, any more suggestions?

    Are there and wildcard options I can use, I don't want to limit the results to a specified 3 digits, I just want something that automatically combines all totals based on the first 3 digits without me having to specify the digits. For example "_ _ _..."

    Sorry if this is getting confusing.

    Thanks
    Last edited by kpruitt44; 02-04-2014 at 12:19 PM.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How to Sum cells that begin with the same sequence of numbers?

    CANAPONE's SUMPRODUCT suggestion works for me - what result did you get using that? Did you change the suggested formula at all?

    SUMIF with a wildcard will only work in column A values are text-formatted
    Audere est facere

  6. #6
    Registered User
    Join Date
    02-04-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to Sum cells that begin with the same sequence of numbers?

    whenever I used Canapone's I get a 0 for all of them. I'm confused about her formula. What does (--( mean?? Is this and array, Do I have to use ctrl+shift+enter?

  7. #7
    Registered User
    Join Date
    02-04-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to Sum cells that begin with the same sequence of numbers?

    Ok, I retried Canapone's and it appears to be working now, thanks!

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to Sum cells that begin with the same sequence of numbers?

    Hi,

    thanks for feedback.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Splitting a string of numbers into separate cells: where to even begin?
    By confused_teacher in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-03-2013, 02:33 PM
  2. [SOLVED] Creating a sequence based on numbers in one column and adding "01,02..." to a new sequence
    By JCR1968 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-05-2013, 08:06 AM
  3. Text Filter-Begin with... (multiple begin withs?)
    By flyflipper in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-16-2010, 06:29 PM
  4. Working with numbers that begin with 0
    By WDP in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 02-16-2009, 01:33 PM
  5. [SOLVED] Dealing with numbers that begin with a 0
    By SueB in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2005, 04:35 PM

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