Pennie has attended:
Word Intermediate course
Excel Introduction course
Merging cells/totals
I have a work sheet that has mulitple items and totals that I wish to merge and have the one item with the totals from each as one:
Example
Baked Beans 31 £17.05
Baked Beans 65 £35.75
Baked Beans 44 £22.00
Beef Burgers 12 £12.60
Beef Burgers 82 £82.95
Beef Burgers 71 £71.40
Need:
Baked Beans 140 £166.95
Hope this makes sense
Pennie Donaghy
RE: Merging cells/totals
Hi Pennie,
Thank you for your question.
Are the three values in the same cell or separate cells?
Regards
Simon
RE: Merging cells/totals
They are in separate cells.
RE: Merging cells/totals
Hi Pennie,
Thank you for the response. Please find a formula below which will concatenate the product with the two totals. This is the sample data I used.
Beans 23 £4.56
beans 45 £7.89
beans 58 £6.59
Burgers 67 £3.56
A1&" "&SUMIF(A1:C4,"Beans",B1:B4)
&" " & SUMIF(A1:C4,"Beans",C1:C4)
The & is used to join separate cell values. The Speech marks with a space represents a space between each value
Cell A1 contains Beans. SumIf function is saying using the data range a1:c4, find the criteria of Beans and the third and final argument is the Sum Range which is the list of numbers.
I hope this makes sense.
Regards
Simon
RE: Merging cells/totals
Hi Pennie,
Update on formula including concatenating £ sign before the total cost.
=A1&" "&SUMIF(A1:C3,"Beans",B1:B3)&" "& "£" &SUMIF(A1:C3,"Beans",C1:C3)
Hope this resolves your question.
Let me know.
Regards
Simon
RE: Merging cells/totals
If its row by row to concatinate:
=A1&" "&B1
Then copy the formula down the column
RE: Merging cells/totals
This was the way I was doing it but thought that there may be a quicker way. Thank you
RE: Merging cells/totals
Hi Pennie,
Slightly confused now as the last answer posted doesn't total the figures for each product.
I thought you wanted to add the quantities together for the beans product and the costs for the bean product.
I will send you the spreadsheet I created to show you how I did it.
If this is not the result you were looking for could you please reply and clarify with more detail perhaps using the spreadsheet I am sending you.
Regards
Simon
Attached files...
Answer Spreadsheet.xlsxRE: Merging cells/totals
Hi Pennie,
Can you please let me know if your Forum Post is now resolved.
Looking forward to your reponse.
Regards
Simon
RE: Merging cells/totals
Hello Pennie,
I was browsing through our forum and came across your question and thought that using subtotals may solve your problem.
I have taken the liberty of creating a Workbook with two sheets...
Sheet1 contains the previously proposed suggestion made very clear.
Sheet2 contains the same data, however, uses the subtotals procedure to produce the figures you seem to require.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
