Sum array with some criteria problem

[sasa_z]

I have Sheet1 and Sheet2.

Sheet1

employeeID (c column)	JobID (d column)	result
23	1

<tbody>
</tbody>

Sheet2 (named CSV1)

employeeID (A column)	JobID (D column))	payment1 (X column)	payment2 (BM column)
23	1	3334	3454

<tbody>
</tbody>

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.