Sum from two different data sheets

[questforexcel]

Hi,

I am hoping this should be an easy and simple one.

I tried using SUMIF and SUMIFS, i likely got something wrong there, it didnt work for me.

I have two sheets. First one has the summary of total counts by specific codes. This is the original data sheet.

The second sheet is the updates list to this original sheet. It has names of people and their counts that need to be updated in these codes.

The unique reference on each sheet is the code column.

I would like a formula to sum the count in the original sheet, with data on the updated sheet for old code and count, and also the new code and count. To give me a summary by each of the below mentioned codes.

Thank you,

List of Codes

AA01

AA02

AA03

AA04

AA05

AA06

Original Data Sheet

Code	Count
AA01	3​
AA02	4​
AA03	2​
AA04	4​
AA05	6​
AA06	7​
Total	26​

Updated Data

Name	Old Code	Count	New Code	Count
Michael	AA04	-1​	AA05	1​
Jim	AA05	-1​	AA04	1​
Pam	AA05	-1​	AA06	1​
Dwight	AA06	-1​	AA05	1​
Creed	AA05	-1​	AA06	1​
		-5​		5​

 

[DanteAmor]

Unfortunately I can't know where your data is and where you want the result.


But assuming that your data is like this in the "original" sheet and the results go in column C

Dante Amor
	A	B	C
1	Code	Count	Original + update
2	AA01	3	3
3	AA02	4	4
4	AA03	2	2
5	AA04	4	4
6	AA05	6	5
7	AA06	7	8
original

Cell Formulas
Range	Formula
C2:C7	C2	=SUMIF(A:A,A2,B:B)+SUMIF(update!B:B,A2,update!C:C)+SUMIF(update!D:D,A2,update!E:E)

And also assuming that the data is like this in the update sheet:

Dante Amor
	A	B	C	D	E
1	Name	Old Code	Count	New Code	Count
2	Michael	AA04	-1	AA05	1
3	Jim	AA05	-1	AA04	1
4	Pam	AA05	-1	AA06	1
5	Dwight	AA06	-1	AA05	1
6	Creed	AA05	-1	AA06	1
update

I await your comments.

 

[questforexcel]

Thank you, yes this exactly how it was, on two different sheets and this is what I was looking

Unfortunately I can't know where your data is and where you want the result.


But assuming that your data is like this in the "original" sheet and the results go in column C

Dante Amor
	A	B	C
1	Code	Count	Original + update
2	AA01	3	3
3	AA02	4	4
4	AA03	2	2
5	AA04	4	4
6	AA05	6	5
7	AA06	7	8
original

Cell Formulas
Range	Formula
C2:C7	C2	=SUMIF(A:A,A2,B:B)+SUMIF(update!B:B,A2,update!C:C)+SUMIF(update!D:D,A2,update!E:E)

And also assuming that the data is like this in the update sheet:

Dante Amor
	A	B	C	D	E
1	Name	Old Code	Count	New Code	Count
2	Michael	AA04	-1	AA05	1
3	Jim	AA05	-1	AA04	1
4	Pam	AA05	-1	AA06	1
5	Dwight	AA06	-1	AA05	1
6	Creed	AA05	-1	AA06	1
update

I await your comments.

Thank you, this is exactly how my sheets were and this is exactly what I was looking to achieve. Appreciate your swift response and effort in helping me solve this.

Have a great day!