+ Reply to Thread
Results 1 to 6 of 6

Pls help, can we concatenate text based on some criteria lik SUMIFS

  1. #1
    Registered User
    Join Date
    03-21-2013
    Location
    dubai
    MS-Off Ver
    Excel 2007
    Posts
    25

    Question Pls help, can we concatenate text based on some criteria lik SUMIFS

    Please help me and let me know how to concatenate text based on some criteria.
    ex.
    CUSTOMER CODE NO OF TRIPS DELIVERY NOTE NOS DATE OF SERVICE
    XYZ 10 123 21 MARCH 13



    We have huge database wiht different customers, with the no of trips, delivery note nos and date of services, i want a summary, which totals no of trips for that particular customer in one colum and another colum, concatenate delivery note nos based on date of service.

    Best regards,
    Shermin

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    566

    Re: Pls help, can we concatenate text based on some criteria lik SUMIFS

    To count the total numbe rof trips for a customer (Assuming Customer is in Col A and Trips is Col B).

    Please Login or Register  to view this content.
    This assumes that you have the same customer listed multiple times in your data in Col A, if this isn't what you mean then can you upload a sheet with example data.

    Not sure what you mean by the second part so will need see an example sheet for this too please.
    Say thanks, click *

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Pls help, can we concatenate text based on some criteria lik SUMIFS

    Hi Shermin and welcome to the forum

    You can pretty much concatenate (join) anything with anything. The simplest way to do that is by using &. So, if the above calc from Harribone gives an answer of 10 for XYZ (in A2), you could say something like...

    ="Customer "&A2" made a total of "SUMIF(A:A,"XYZ",B:B)&" Trips"
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    03-21-2013
    Location
    dubai
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Pls help, can we concatenate text based on some criteria lik SUMIFS

    Hi! I have attached my file.
    the first sheet has data, and another sheet i want to get the answer, total of trips & the delivery note nos for those customers, on the given date range.
    Pls help.
    Best regards,
    Shermin
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    566

    Re: Pls help, can we concatenate text based on some criteria lik SUMIFS

    Please Login or Register  to view this content.
    Put this in cell B3 of the answer sheet and copy down to give the total number of trips.
    I don't know how to list the delivery notes though in one cell though sorry.

  6. #6
    Registered User
    Join Date
    03-21-2013
    Location
    dubai
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Pls help, can we concatenate text based on some criteria lik SUMIFS

    Hi! I am not clear with your formulae,
    i have attached the file,
    the first sheet has data, and another sheet i want to get the answer, total of trips & the delivery note nos for those customers,(by concatenating those delivery notes nos with a comma) on the given date range.
    Attached Files Attached Files

+ 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