# How to combine data and calculate in Excel with formula

``````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?

• 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

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)`