Convert Sumif to Sumproduct not working....

[markgrnh]

Good afternoon,

I am trying to convert a Sumif formula to a Sumproduct formula as the reference is using another sheet that is not always open which results in an error. Unfortunately all I get from the Sumproduct formula is a #N/A

Any help would be appreciated,

This is my Sumif formula : =SUMIF('S:\Finance Department\Credit Control Debtor Reporting Tool\[Debtor Checker V5.xlsm]CML Aged Debtors'!$BL:$BL,C2,'S:\Finance Department\Credit Control Debtor Reporting Tool\[Debtor Checker V5.xlsm]CML Aged Debtors'!$K:$K)

That works fine, but only when the sheet is open so I tried the Sumproduct for when it closed, and this is the Sumproduct formula:

=SUMPRODUCT(--('S:\Finance Department\Credit Control Debtor Reporting Tool\[Debtor Checker V5.xlsm]CML Aged Debtors'!$BL1:$BL10000=C2),'S:\Finance Department\Credit Control Debtor Reporting Tool\[Debtor Checker V5.xlsm]CML Aged Debtors'!$K1:$K10000)

Any ideas what I am doing wrong?

Thanks

Mark

 

[Dave Patton]

Does your SumProduct formula work when the files are open?
You could review the formula with just a few rows and known expected results.

 

[markgrnh]

Does your SumProduct formula work when the files are open?
You could review the formula with just a few rows and known expected results.

Hi Dave it doesn't but the Sumif formula does.

Thanks
Mark

 

[Dave Patton]

Did you try reviewing the formula
You could review the formula with just a few rows and known expected results.

 

[FormR]

Any ideas what I am doing wrong?

Do you have any #N/A errors in this range 'S:\Finance Department\Credit Control Debtor Reporting Tool\[Debtor Checker V5.xlsm]CML Aged Debtors'!$K1:$K10000?

SUMIFS() can be a little more resilient against such problems.

 

[markgrnh]

I've turned off the computer now but will try your suggestions in the morning so thanks for your replies and I will update you soon

 

[markgrnh]

Thanks FormR, yeah I had one rogue N/A on the sheet that was messing it all up, once I fixed that, it fixed the Sumproduct formula. Thanks for both your replies