Evaluate and Calculate from Closed Workbook

[SMHelp]

Hello,

I'm going slightly mad trying to figure out how I can do this, so I would really appreciate any guidance!!

I have two workbooks - one contains references and figures (A), one contains a total (B). The idea is to check one column in (A) for a specific text value, then add numerical values in another column in (A), producing the total in a cell in (B).

Here are the issues:

1) Can't use SUMIF, as it needs to calculate on a closed workbook;
2) Can't use SUMPRODUCT, as text value in column (A) is based on a VLOOKUP and has some #N/A values in the column, which fails the SUMPRODUCT;
3) Tried using IF statement, which works on single cell text references in (A), but gives a #VALUE error when using even a 3 cell range (and can't figure out why - have checked all values in the range in (A) and they're all the same types)

Any ideas greatly appreciated!!

Thanks

Sian

 

[Domenic]

Try the following array formula . . .

=SUM(IF(1-ISNA('C:\Users\Domenic\Desktop\[Book1.xlsm]Sheet1'!$A$2:$A$10),IF('C:\Users\Domenic\Desktop\[Book1.xlsm]Sheet1'!$A$2:$A$10="X",'C:\Users\Domenic\Desktop\[Book1.xlsm]Sheet1'!$B$2:$B$10)))

. . . confirmed with CONTROL+SHIFT+ENTER. Change the references accordingly.

Hope this helps!

 

[SMHelp]

Thank you SO MUCH!!

That works a dream!!

 

[Domenic]

That's great, you're very welcome, cheers!