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

**Shermin** · 03-21-2013, 03:32 PM

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

**Harribone** · 03-21-2013, 04:12 PM

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.

**FDibbins** · 03-21-2013, 04:29 PM

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"

**Shermin** · 03-21-2013, 05:21 PM

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

**Harribone** · 03-21-2013, 05:38 PM

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.

**Shermin** · 03-22-2013, 04:31 AM

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.