0

Please find attached mention 3 sheets with same heading

Section ID  item count  amt gross amt

Sheet 1 and sheet 2 need to add together for whatever matching section and ID column, Need to add together if section and ID repeating in same sheet or different sheet of sheet 1 and sheet 2 .

Sheet 3 is a negative figure as it item return, so what ever section and ID repeat in sheet 3 only add together .

Here I can only do by pivot in separate and vlookup for each column and its something like complicated.

Is there any better way for doing it?

enter image description here

  • Your question is not very clear. Do you mean that to be included in the result, an ID must be repeated on a single worksheet? That would explain why only AA and AE are listed in the result, but why is AA listed twice? AA is not repeated on Sheet3. – Bandersnatch Apr 16 '18 at 15:44
  • sorry for Inconvenience, Just updated whole thing – safwan Apr 17 '18 at 13:04
  • What you described is kind of a database task, Excel is not very good in them. I don't think we could suggest you a much better solution. Maybe a SUMIFS instead of VLOOKUP. – Máté Juhász Apr 17 '18 at 13:21
  • Hii Mate , can you please explain how can we do by SUMIFS – safwan Apr 17 '18 at 15:44
0

EDIT:

The formulas have been modified to add the data in the Count, Amount, and Gross columns only where there are duplicate values for both Location and ID. The Result table is located on a separate Sheet.

This formula, filled right and down (3 rows only) from C3 in the screenshot below:

=SUMIFS(Sheet1!C$3:C$6,Sheet1!$B$3:$B$6,$B3,Sheet1!$A$3:$A$6,$A3)+SUMIFS(Sheet2!C$3:C$6,Sheet2!$B$3:$B$6,$B3,Sheet2!$A$3:$A$6,$A3)

gives the results for the first part. And this formula, filled right and down from C4, does the second part:

=SUMIFS(Sheet3!C$3:C$6,Sheet3!$B$3:$B$6,$B6,Sheet3!$A$3:$A$6,$A6)

enter image description here

If this answer is not satisfactory, please consult with someone who can help you clearly explain what you are trying to do.

  • Thankyou , really what we expect is ,formula which can take column including A and B in result sheet .By checking column A and make unique of column B .And am not sure it is possible in excel – safwan Apr 18 '18 at 5:43
  • for result : in sheet 1 if column A = B repeat in same sheet 1 or match with sheet 2 make it consoldate and sum ,if A= B is unique of sheet 1 or 2 then as it is same appear in result .Result sheet is the answer of Sheet 1,2,3 total work – safwan Apr 18 '18 at 9:34
  • Sorry, I am having trouble understanding you. Are you saying you need a formula to list the unique IDs from sheet 1 and 2? And then the totals should be for Sheets 1, 2 and 3? Before you said Sheet 3 should be listed and totaled separately because the numbers were negative. – Bandersnatch Apr 18 '18 at 13:51
  • Note that the Result table in my answer is exactly what you have shown in your question. Try to clearly explain what you need that is different. – Bandersnatch Apr 18 '18 at 13:53
  • Really sheet name result is blank sheet and i need answer same like shows in result sheet. Sheet 1 and sheet 2 add together by combining ,sheet 1 and sheet 2 add if section and ID matches. There are ID and section of sheet 1 repeat in sheet 1 and also there is chance to repeat in sheet 2 also so consoldate and add it .In Sheet 3 is negative figure so if ID and section repeat in same sheet itself mean will consoldate and add negative figure, sheet 3 not add with sheet 1 and 2 . – safwan Apr 18 '18 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.