#value on sum & countifs formulas when external files are closed.

[Shamz41]

Hi

I manage to get help on this table in the attached spreadsheet in which the sum formulas work if the 2 files are opened together but when I close the file where the values are extracted, from I receive #value across all cells that the formulas are placed in. Need some help on the below formula.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JohnTopley]

Try

=SUMPRODUCT(('[Countifs Total.xlsx]worksheet1'!$J$2:$J$1000={"Mike","John"})*('[Countifs Total.xlsx]worksheet1'!$B$2:$B$1000=C$2)*('[Countifs Total.xlsx]worksheet1'!$A$2:$A$1000=SUBSTITUTE(B$2," ","")))


=SUMPRODUCT(('[Countifs Total.xlsx]worksheet1'!$B$2:$B$1000=C$2)*('[Countifs Total.xlsx]worksheet1'!$A$2:$A$1000=SUBSTITUTE(B$2," ","")))-C3

With C3=0, you cannot possibly get a negative result for the second formula

With SUMPRODUCT you must NOT use whole column ranges i.e A:A