I have Sheet1 and Sheet2.
Sheet1
<tbody>
</tbody>
Sheet2 (named CSV1)
<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
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