Want a sum of figures when 2 rows in different worksheets are a match

[Nitaa]

Hi, I have different worksheets in a workbook. I need one of the worksheets to sum up the figures from the other 2 worksheets when the row inputs in those worksheets are a match.

The input in one worksheet (eg row 1: Salary) may not be in the same row as worksheet 2. I tried using a if and or formula - it looked as it worked at one time but now I am not getting the results I want.

=IF(OR($A38='SG TECH BUDGET 2018'!$A32,'SGWWL Prdt IDP BUDGET 2018'!$A38='SG IDP BUDGET 2018'!$A32),SUM('SG TECH BUDGET 2018'!I32,'SG IDP BUDGET 2018'!I32))

 

[Aladin Akyurek]

Care to post a small sample illustrative of your data along with the manually calculated desired result?

 

[Nitaa]

Example - worksheet 1

in Col A row 12, I have code "54001" for 5000 under Jan column D and 6000 under Feb for Col E

Worksheet 2

col A row 25, I have the same code "54001" for 4000 under Jan Col D and 2500 under Feb for Col E

So I want in worksheet 3 to sum up 54001 in worksheet 1 & 2 under col D and E. Col D should sum 9000 and Col E 8500.

 

[Aladin Akyurek]

Care to post a small sample using one of the possible methods listed in https://www.mrexcel.com/forum/about-...ml#post2507729, along with a desired result, manually calculated?

 

[C with no eyes]

Assuming the code "54001" only appears once on each spreadsheet the following will work:

I have assumed the heading of the code is called code. I have also assumed there is a heading on column D in your third worksheet that will exactly match the ones you wish to add up. e.g. they both say Jan, not one says Jan but one says January.

=ifna(index([COLOR=#333333]'SG TECH BUDGET 2018'[/COLOR]!$1:$1048576,match("code",[COLOR=#333333]'SG TECH BUDGET 2018'[/COLOR]!$1:$1,0),match(d$1,[COLOR=#333333]'SG TECH BUDGET 2018'[/COLOR]!$1:$1,0)),0)+ifna(index([COLOR=#333333]'SGWWL Prdt IDP BUDGET 2018'[/COLOR]!$1:$1048576,match("code",[COLOR=#333333]'SGWWL Prdt IDP BUDGET 2018'[/COLOR]!$1:$1,0),match(d$1,[COLOR=#333333]'SGWWL Prdt IDP BUDGET 2018'[/COLOR]!$1:$1,0)),0)

Depending on the version of excel you are using you may need to change IFNA to IFERROR.