Formula help

[ThatguyLFS]

Hi,

I am having trouble trying to get my spreadsheet to update a formula when another workbook that it's linked to is closed. I was reading somewhere that the COUNTIF function won't work with a closed document but a SUMPRODUCT function would. I just don't know how to convert my current function to the sumproduct function.

Here is my current formula. I really appreciate any help on this.

=COUNTIFS('[Filename.xlsx]Sheet1'!$H$1:$H$65536, "1", '[Filename.xlsx]Sheet1'!$E$1:$E$65536, "Hardware - Loaner" )

 

[VoG]

Try

=SUMPRODUCT(--('[Filename.xlsx]Sheet1'!$H$1:$H$65536=1), --('[Filename.xlsx]Sheet1'!$E$1:$E$65536="Hardware - Loaner"))

 

[ThatguyLFS]

Thank you very much! Would I also be able to add another array that would SUM another row if they had those criteria? So it would be the same SUMPRODUCT formula, which would then add up, let's say column Q if it met the criteria in this formula?

=SUMPRODUCT(--('[Filename.xlsx]Sheet1'!$H$1:$H$65536=1), --('[Filename.xlsx]Sheet1'!$E$1:$E$65536="Hardware - Loaner"))

 

[VoG]

Try

=SUMPRODUCT(--('[Filename.xlsx]Sheet1'!$H$1:$H$65536=1), --('[Filename.xlsx]Sheet1'!$E$1:$E$65536="Hardware - Loaner"),'[Filename.xlsx]Sheet1'!$Q$1:$Q$65536)

 

[ThatguyLFS]

Thank you very much, if you were here I'd give you a handshake. Thanks a lot!