#Value Error fix when using multiple files

[dportillo]

I have been searching everywhere and I cant seem to figure out how to solve this issue, any help would be much appreciated. The issue is that at my work we build our financial model using multiple excel files and link from one to the other. The issue I have is that when using sumif/sumifs I get the #value error unless all the files that are being linked are open (as you can imagine this can be kinda annoying). I am trying to return a value based on a couple conditions, both month and year. Below is a basic example of the issue, the left table is one file and the right is a different independent file. If I want to enter a formula in file #2 that would return the sum of payments from the table #1 based on the month (row 4) and year (row 3), I would normally use sumifs. But would like to get away from this to avoid the #value error. Also, attached an excel file as an example of what i am trying to do. Thanks in advance!

[canapone]

Hi,

an attempt.

Using the data layout you shared, maybe sumproduct could help.

=SUMPRODUCT($B$5:$F$6*($B$2:$F$2=I2))

Regards

[dportillo]

Can you use sumproduct using two conditions (month and year - rows 3 and 4)?

[:) Sixthsense :)]

Let us know your Sumif formula so that we can give you the exact Sumproduct formula

[dportillo]

The formula I use now is (=+SUMIFS($B$5:$F$5,$B$3:$F$3,I3,$B$4:$F$4,I4)+SUMIFS($B$6:$F$6,$B$3:$F$3,I3,$B$4:$F$4,I4). Looking up by year and month. If linking to another file it does not work and returns the #value error. Attached below is the example.

[canapone]

Hi,

sumproduct is quite flexible.

In I5 and to the right:

=SUMPRODUCT($B$5:$F$6*($B$3:$F$3=I3)*($B$4:$F$4=I4))

Regards