How to combine data and calculate in Excel with formula
safwan
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?
Bandersnatch
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)
If this answer is not satisfactory, please consult with someone who can help you clearly explain what you are trying to do.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
Excel formula to combine first names
- 2
How to combine these 2 formula
- 3
Excel Formula: How to calculate amount to add, given target average price
- 4
Excel formula to calculate amount of overtime
- 5
Excel Formula to calculate Specific amount
- 6
Excel Formula Compare Data
- 7
How to combine a trim and substitute formula?
- 8
Combine two long IF statements on Excel into one formula
- 9
Math - how to calculate arccos formula?
- 10
How to calculate an formula in a different timeframe
- 11
Math - how to calculate arccos formula?
- 12
Excel Formula to Calculate the Third Point in a Triangle
- 13
Excel Formula to Calculate Percentage on Different Sheet
- 14
Match Function Excel 2013 - Formula will not calculate
- 15
EXCEL: Formula to Calculate the Standard Deviation for Subsets of a Range
- 16
Challenging Formula in excel to calculate successive remainder
- 17
How to ignore filtered-out data in Excel formula
- 18
How do I solve the string formula for this dependent data validation in Excel?
- 19
Combine column data of two sheets based on formula
- 20
How to add formula to Excel
- 21
Excel - how to NOT a formula?
- 22
Excel formula to show data trends
- 23
Excel changes simple data into formula
- 24
Struggling with formula and returned data in Excel
- 25
How can I combine 2 lists of data in EXCEL
- 26
How to combine related data in Excel using a common field and averages
- 27
how to get real formula from EXCEL formula
- 28
How to Combine IF statements in Excel
- 29
Combine multiple data frames and calculate average
Comments