Retrieving numbers from another file stored under a different directory

[pvp]

Hello,

I am having problems with a sumproduct formula (An alternative is appreciated).
In file Copy Compare 2011-2012 I would like to retrieve the Revenue of clients (criterions: Name of Client Group, Service provided, month) from the source file
ABC-Analyse.
For using the criterion "month" The formula needs to move within the source file. Can this be done with an offset, address function embedded in the sumproduct/ some alternative one?
Even using only the two criterions Client Group, Service provided I receive the error message "value#"
Can someone help me on this?

I would appreciate it very much.

Best regards and thanks in advance


Philip

[NBVC]

Your sum range in the Sumproduct is not the same size as the criteria ranges...
Also, when you "multiply" the conditions, if there is text in the sum cells, you will get the error, separate out the sum ranges with commas instead....

i.e.

=SUMPRODUCT((('[ABC-Analyse.xls]ABC'!$D:$D)=$A5)*(('[ABC-Analyse.xls]ABC'!$I:$I)="Translation"),('[ABC-Analyse.xls]ABC'!$BB:$BB))

Note: using whole column references with Sumproduct is not efficient... you should try to limit the ranges to absolute minimum needed for best results.

[pvp]

Hi thanks a lot.
I have another question. An alternative to the sumproduct would be a Sum function combined with two or more If functions.
What I would like to do in addition is to let the formula retrieve the figure of a client of the correct month
even if I add a new column in the range of the source file from which I retrieve the monthly figures
I inserted a matvch function in line 3 but I do not know how to combine it with the sum formula to arrive at what I want the formula to do.
Is this feasible? How do I have to adapt the formula?
Thanks a lot once again.

[NBVC]

using the original 2 files, maybe:

Please Login or Register  to view this content.

edit filenames and references to suit...