Convert SUMIF to SUMPRODUCT

[tylerstoehr]

Hello,

I am having a lot of trouble converting a SUMIF to a SUMPRODUCT formula. Would anyone be able to help me out?

=SUMIF('\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$A:$A, D2,'\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$I:$I)

Where SPREADSHEET is the source file, SHeet1 is the source sheet, column A contains file numbers. Column D in the working file contains file numbers to be matched to column A in SPREADSHEET Sheet1, and SPREADSHEET Sheet1 column I contains dollar amounts to be summed.

Thanks for your help!

 

[Aladin Akyurek]

Try to avoid whole column references for reasons of efficiency...

=SUMPRODUCT(('\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$A$2:$A$1000=D2)+0,'\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$I$2:$I$1000)

 

[tylerstoehr]

Thank you for the info, i'll limit the columns from now on.

Unfortunately I still only get #VALUE!, including when calculating. (Even when using CTRL+SHIFT+ENTER)

The SUMIF formula I had works within the same file (Minus the UNC Path of course), when comparing to another sheet within that file, so the logic seems sound.


My only other option is to have the working file pull Sheet1 from SPREADSHEET each time the file is opened, which subsequently locks SPREADSHEET and introduces numerous other issues in our workflow.

I really appreciate your help!

 

[Aladin Akyurek]

Thank you for the info, i'll limit the columns from now on.

Unfortunately I still only get #VALUE!, including when calculating. (Even when using CTRL+SHIFT+ENTER)

The SUMIF formula I had works within the same file (Minus the UNC Path of course), when comparing to another sheet within that file, so the logic seems sound.


My only other option is to have the working file pull Sheet1 from SPREADSHEET each time the file is opened, which subsequently locks SPREADSHEET and introduces numerous other issues in our workflow.

I really appreciate your help!

If

=SUMIF('\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$A$2:$A$100, D2,'\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$I$2:$I$1000)

works as expected while the target workbook is open,

=SUMPRODUCT(('\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$A$2:$A$1000=D2)+0,'\\UNCPATH\[SPREADSHEET.xlsx]Sheet1'!$I$2:$I$1000)

will also succeed whether the target book is open or not.

 

[tylerstoehr]

I would like to thank Aladin for all the help he provided me.

I PM'ed him for assistance with my full formula, since i did not want full file paths and names on a public forum.
I am posting my solution here for reference.

His formula was exactly correct, my issue was that the SUMPRODUCT function seems that it cannot compare to the output of a formula, and i had to create an extra column and paste the values then convert to number for it to work correctly.

Quite annoying, but at least i got it working with his help.

Thanks again Aladin!!!

 

[Aladin Akyurek]

I would like to thank Aladin for all the help he provided me.

I PM'ed him for assistance with my full formula, since i did not want full file paths and names on a public forum.
I am posting my solution here for reference.

His formula was exactly correct, my issue was that the SUMPRODUCT function seems that it cannot compare to the output of a formula, and i had to create an extra column and paste the values then convert to number for it to work correctly.

Quite annoying, but at least i got it working with his help.

Thanks again Aladin!!!

You are welcome.