SUM formula, data from another workbook

[Hudson512]

Hi,

I'm having problems trying to summarise some data from another (closed) workbook.

The summary page is currently in the same workbook but it would be a lot better if it could be in a seperate workbook. The formula I'm using is below

=SUM(('Sheet1'!F3:F20="Name")*('Sheet1'!G3:G20="Yes"))

Is this possible?

Thanks in advance

 

[Andrew Poulsom]

Yes, it's possible. Open the other workbook and enter your formula in the target workbook, pointing at the cells with your mouse while you do so. It will look something like this:

=SUM(([Book2.xlsm]Sheet1!F3:F20="Name")*([Book2.xlsm]Sheet1!G3:G20="Yes"))

 

[Hudson512]

Hi,

Thanks for the quick response, really appreciate it.

I have put the formula in that you suggested and it has worked better than my previous attempts, it doesn't produce an error message now.

The value has gone to zero, where it should be 4.

Any suggestions? The formula worked ok when in the same sheet.

Thanks again

 

[Andrew Poulsom]

Did you remember to press Ctrl+Shift+Enter? You could also try the non-array version:

=SUMPRODUCT(--([Book2.xlsm]Sheet1!F3:F20="Name"),--([Book2.xlsm]Sheet1!G3:G20="Yes"))

 

[Hudson512]

Hi,

No I didn't remember! Got it in one! I realised just after I'd posted the thread! Sorry about that.

You've been great, thank you so much for your help.

Formula works great