LinkBack URL
About LinkBacks
Column 1 Column 2 Column 3
Bob Sales 45
Bob Sales 498
Bob Sales 65
How do I merge the cells together resulting in a total in Column 3 of (45+498+65).
Bob Sales 608
The whole report is lines like this over and over.
Thanks!!
Column 1 Column 2 Column 3
Bob Sales 45
Bob Sales 498
Bob Sales 65
How do I merge the cells together resulting in a total in Column 3 of (45+498+65).
Bob Sales 608
The whole report is lines like this over and over.
Thanks!!
Hello,
I'm not 100% sure what you are looking for, but suggest you investigate using SUMIFS which should allow you to add up the values in column 3 using the entries in Columns 1 and 2 as the matching criteria.
Simon.
Thanks Simon, that almost works. I think what I am trying to do is more complicated. For example, I have a list of clients who have multiple records - the name of the company matches and the client number matches but they have different sales amounts. I want to merge the lines with the duplicate records but creating a total of the sales column/customer record.
If thats not possible, just create a sales total in the next column for each customer, I can sort and delete the duplicates after. I just want to end up with one customer line for each customer. But there are 3000 records and I don't know how to do this without do eaching client separately.
I don't know if there is a criteria that will say if column a and column b are equal to the record below create a sum of the sales for all 3 records in the next column. (I know.....confusing)
COMPANY CUSTOMER NO SALES
ABC COMPANY 00456 10000
ABC COMPANY 00456 2500
ABC COMPANY 00456 2000
123 COMPANY 00852 3000
123 COMPANY 00852 3500
XYZ COMPANY 00147 1200
XYZ COMPANY 00147 1100
RESULT
ABC COMPANY 00456 14500
123 COMPANY 00852 6500
XYZ COMPANY 00147 2300
OR:
ABC COMPANY 00456 10000 14500
ABC COMPANY 00456 2500
ABC COMPANY 00456 2000
123 COMPANY 00852 3000 6500
123 COMPANY 00852 3500
XYZ COMPANY 00147 1200 2300
XYZ COMPANY 00147 1100
(Sorry if it takes all the spacing out separating the data)
Thanks!
Last edited by birdie1234; 08-02-2013 at 09:16 PM. Reason: spacing not working
Copy 'Company' & 'Customer No' columns to a different sheet with heading & select the data then go to Data >> Remove Duplicates. Uncheck 'Sales' if already showing on the columns in Remove Duplicate window, then click OK. So this keep only UNIQUE list of Companies & Customer No. Then use SUMIFS,
Assuming Companies are entered in Sheet2!A2 to down & Customer No from B2 to down, so in C2 use
=SUMIFS(Sales,Company,A2,Customer_No,B2)
and copy down.
Also check about Pivot Table.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
And the best solution (to my opinion) is to use pivot table for that.
In that case you don't have to identify all possible names.
Pivot Table will do that for you.
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.
I had someone help me with the spreadsheet and neither the SUMIFs or the Pivot Table were quite working. I did some tweeking to the spread sheet and now I only need to do a VLOOKUP from one sheet to another to find the missing value.
However, the first cell will pull in the information correctly so the formula is ok for that. But the formula will not copy correctly (I did both paste and paste forumla) It will just repeat the same value from the first cell. I did not lock down my value A2. If you click on the next cell it will recognize in the formula bar that it is on A3, but will not populate the info. If you hit enter, it will pick up the information and be correct. You end up doing this for ever cell which defeats the purpose of copying a formula.
Any suggestions.
=VLOOKUP(A2,SOURCEWORKSHEET,2,FALSE)
I also tried a range of A2:A149 instead of just A2 but it still will not pick up the values for A3-A149 when copying the formula.
Your sheet calculation Mode will be in 'Manual' mode. Go to Formula >> Calculation Options & change to 'Automatic'
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks