Sum array with some criteria problem

[sasa_z]

I have Sheet1 and Sheet2.

Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]employeeID (c column)[/TD]
[TD]JobID (d column)[/TD]
[TD]result[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (named CSV1)

[TABLE="width: 500"]
<tbody>[TR]
[TD]employeeID (A column)[/TD]
[TD]JobID (D column))[/TD]
[TD]payment1 (X column)[/TD]
[TD]payment2 (BM column)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1[/TD]
[TD]3334[/TD]
[TD]3454[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In Result column on sheet1 I want to have sum of columns (payment1,payment2) where values employeeID and JobID are the same in both sheets.

I tried this {=SUM(IF($C5='CSV1'!$A$2:$A$151,$D5='CSV1'!$H$2:$H$151)*('CSV1'!$X$2:$X$151+'CSV1'!$BM$2:$BM$151))} and it worked but when some rows are deleted formula got #ref ! error so I decided to use offset as well
{=SUM(IF(OFFSET($A$1,ROW()-ROW($B$1),1)='CSV1'!$A$2:$A$151,OFFSET($A$1,ROW()-ROW($B$1),2)='CSV1'!$H$2:$H$151)*('CSV1'!$X$2:$X$151+'CSV1'!$BM$2:$BM$151))}

and got #ref ! error with "a value used is of the wrong data type

Any help appreciated

 

[sasa_z]

YOu mean not to use offset? I don't know any other way to resolve an issue when some rows or cells are deleted in C,D columns.

 

[Aladin Akyurek]

YOu mean not to use offset? I don't know any other way to resolve an issue when some rows or cells are deleted in C,D columns.

Yes, I mean that. And before proceeding on adding and deleting rows, try to explain what happens with the formula you are already given?

 

[sasa_z]

I got REF! errors. Try to delete some cells in C and D columns on sheet1 and you will get the same error.

 

[Aladin Akyurek]

I got REF! errors. Try to delete some cells in C and D columns on sheet1 and you will get the same error.

Does the formula work? Try first to answer this question.

 

[sasa_z]

Yes, it works with your worksheet (don't know the reason why not working on my worksheet). I had working formula before I started this post as I stated. It is a little different from yours but it was working. My main goal was to add offset to resolve issue with formula when some cells are deleted. Ty

 

[Aladin Akyurek]

First things first.

Try to figure out why the formula does not succeed in your spreadsheet. Once that's cleared up, I'll proceed to the "delete/add" issue.