Need help with if is error and sumproduct excel formula

[gmfston]

Hello Ninjas,

I created a combined if is error and sumproduct formula but it is populating zero values. I am having difficulty determining what the issue is. I am looking for a formula that looks at the matching data in columns a, b and c and populates the amounts from sheet 2, column d to column e, sheet 1. The expected results are populated in column F, sheet 1.

Best regards,
Gina

 

[John Jairo V]

Hi, @gmfston!

You can try this formula:
=SUMIFS('Sheet 2'!D:D,'Sheet 2'!A:A,A7,'Sheet 2'!B:B,B7,'Sheet 2'!C:C,C7)

P.D. Your Data in C column of Sheet2 have an additional space at the end of the phrases. I delete it (with TRIM function and copy-paste as values in C). Blessings!

 

[p45cal]

1. As John Jairo V said, trim the values in column C of sheet2.

2. To adjust your formulae, in C9 instead of:

=IF(ISERROR(SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9),('Sheet 2'!$B$6:$B$17='Sheet 1'!B9),('Sheet 2'!$C$6:$C$17,'Sheet 1'!C9),'Sheet 2'!$D$6:$D$17)),0,SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9),('Sheet 2'!$B$6:$B$17='Sheet 1'!B9),('Sheet 2'!$C$6:$C$17,'Sheet 1'!C9),'Sheet 2'!$D$6:$D$17))

try:

=IF(ISERROR(SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9)*('Sheet 2'!$B$6:$B$17='Sheet 1'!B9)*('Sheet 2'!$C$6:$C$17='Sheet 1'!C9),'Sheet 2'!$D$6:$D$17)),0,SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9)*('Sheet 2'!$B$6:$B$17='Sheet 1'!B9)*('Sheet 2'!$C$6:$C$17='Sheet 1'!C9),'Sheet 2'!$D$6:$D$17))

I'll try to highlight the differences below but I may not have caught them all. In any event, it seems to give the correct results.

=IF(ISERROR(SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9),('Sheet 2'!$B$6:$B$17='Sheet 1'!B9),('Sheet 2'!$C$6:$C$17,'Sheet 1'!C9),'Sheet 2'!$D$6:$D$17)),0,SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9),('Sheet 2'!$B$6:$B$17='Sheet 1'!B9),('Sheet 2'!$C$6:$C$17,'Sheet 1'!C9),'Sheet 2'!$D$6:$D$17))
=IF(ISERROR(SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9)*('Sheet 2'!$B$6:$B$17='Sheet 1'!B9)*('Sheet 2'!$C$6:$C$17='Sheet 1'!C9),'Sheet 2'!$D$6:$D$17)),0,SUMPRODUCT(('Sheet 2'!$A$6:$A$17='Sheet 1'!A9)*('Sheet 2'!$B$6:$B$17='Sheet 1'!B9)*('Sheet 2'!$C$6:$C$17='Sheet 1'!C9),'Sheet 2'!$D$6:$D$17))

 

[gmfston]

Hello John,

Thank you so much. The revisions worked.

Best regards,
Gina