Use the Excel formula to calculate the amount difference

An Excel file contains two years of data in two sheets. Each year's data is composed of a key and its corresponding values. The keys for two years are the same, it is required to calculate the difference of the value corresponding to the same key in the past two years, and start to prepare a program for computation. However, after careful consideration, you can use the formulas in Excel to implement this function.
The specific method is as follows:
Assume there are two existing sheets, namely, sheet1 and sheet2,
The content is as follows:
Sheet1

Primary Key	Amount (2006)
1	10
2	20
3	30
4	40
5	50
6	60

Sheet2

Primary Key	Amount (2007)
1	1
2	55
3	5
4	30
6	8

Steps
1. traverse the primary keys in sheet1 and find the primary keys that appear in both sheet1 and sheet2.
Use the function = If (countif (,) = 1 ,,"")
Example = If (countif (sheet2! A: A, sheet1! A2) = 1, sheet1! A2 ,"")
Use primary key columns of the local sheet3

The countif function is used to query the number of times a primary key in sheet1 appears in sheet2,
If the number of times is equal to 1, this primary key also exists in sheet2, that is, it has the same.

Use the IF function to determine if the primary key of sheet1 is the same.
Add a space if there are no identical ones.

2. Fetch the corresponding amount from the primary key retrieved in step 1 to sheet1.
Use the function = sumif (,,)
Example = sumif (sheet1! A: A, A3, sheet1! B: B)
2006 columns using the amount of the local sheet3

Sumif is a conditional addition function, that is, only the content of the elements that meet the conditions is added up.

3. Use the same method in step 2 to get the amount in sheet2.
2007 columns using the amount of the local sheet3


4. Use the amount obtained in step 3 minus the amount obtained in step 2 to obtain the expected result.

Use functions-
Example = C3-B3
Use the difference of the local sheet3 column 2007-2006

Sheet3 (result)

	Amount	Amount	Difference
Primary Key	2006	2007	2007-2006
1	10	1	-9
2	20	55	35
3	30	5	-25
4	40	30	-10
	0	0	0
6	60	8	-52

The following red difference is implemented using the condition format.

Amount Calculation Excel