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

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(CSV1!$A$2:$A$100=A2,IF(CASV1!$D$2:$D$100=B2,CSV1!$X$2:$X$100+CSV1!$BM$2:$BM$100)))

where A2 = 23 and B2 = 1.

 

[sasa_z]

Thank you Aladin for you answer. I tried and it doesn't work. When I tried =IF(CSV1!$A$2:$A$100=A2 it founds only first value in a range, it doesn't proceed further

 

[Aladin Akyurek]

Thank you Aladin for you answer. I tried and it doesn't work. When I tried =IF(CSV1!$A$2:$A$100=A2 it founds only first value in a range, it doesn't proceed further

Edited for the references, the formula is: In E2 of Sheet1 control+shift+enter, not just enter, and copy down...

=SUM(IF('CSV1'!$A$2:$A$100=C2,IF('CSV1'!$D$2:$D$100=D2,'CSV1'!$X$2:$X$100+'CSV1'!$BM$2:$BM$100)))

This should work as intended.

 

[sasa_z]

No, it doesn't work. Of the the problem is IF('CSV1'!$A$2:$A$100=C2 proceed only first value. Try to create simple range in excel and you will see.

 

[Aladin Akyurek]

No, it doesn't work. Of the the problem is IF('CSV1'!$A$2:$A$100=C2 proceed only first value. Try to create simple range in excel and you will see.

Did you apply control+shift+enter to the formula before copying it down?

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done with success, Excel itself puts a pair of { and } around the formula in recognition.

 

[sasa_z]

Sure, I did that.

 

[Aladin Akyurek]

Sure, I did that.

See the workbook which shows the set up: https://www.dropbox.com/s/lmg87bie9w2jqzp/sasa_z%20conditional%20sum.xlsx?dl=0

 

[sasa_z]

Hm. I did literally the same in my excel but not sure why it behaves differently. It just catch first value in a range, not sure about the reason.

By the way, I tried to use offset in your excel but it didn't work. I need to use offset because from time to time, some rows are deleted. Please see below how I used offset.

{=SUM(IF('CSV1'!$A$2:$A$100=OFFSET($C$1,ROW()-ROW($D$1),0),IF('CSV1'!$D$2:$D$100=OFFSET($C$1,ROW()-ROW($D$1),1),'CSV1'!$X$2:$X$100+'CSV1'!$BM$2:$BM$100)))}

Thank you

 

[Aladin Akyurek]

Hm. I did literally the same in my excel but not sure why it behaves differently. It just catch first value in a range, not sure about the reason.

By the way, I tried to use offset in your excel but it didn't work. I need to use offset because from time to time, some rows are deleted. Please see below how I used offset.

{=SUM(IF('CSV1'!$A$2:$A$100=OFFSET($C$1,ROW()-ROW($D$1),0),IF('CSV1'!$D$2:$D$100=OFFSET($C$1,ROW()-ROW($D$1),1),'CSV1'!$X$2:$X$100+'CSV1'!$BM$2:$BM$100)))}

Thank you

Don't. Try to implement the set up you are given.