SUMIF - counting across different spreadsheets

[JoFo]

I'm starting a new thread, as I'm not sure if people have seen my reply in the old thread - please let me know if this is a problem!

New day - new problem!

My manager has now asked for a SUMIF summary to be on a separate spreadsheet (so that she can look at it along with other summaries)

Unfortunately, using this formula:
=SUMIF('S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127)

just brings up a #Value! unless the spreadsheet it is linking to is open. If the linked to spreadsheet is opened, then closed, then I get the figures untill I close the summary spreadsheet. Then, when I reopen the summary spreadsheet, and click on Update (which I would need to if any of the other sections that this spreadsheet links to had been updated), I get #Value! again.

I can't see why this doesn't work when the more simple formulae (eg a straight ='S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Summary'!$B$21) do. What am I doing wrong?

[Dave Peterson]

=sumif() doesn't work with closed workbooks.

You could write it as =sum(if(...))
(and enter as an array formula)

But you can't use the whole column.

And I'm confused about what range you're checking with: $1:$65536

=sumproduct() can work with closed workbooks, too:

=sumproduct(--(thatlongstring...!$a$3:$a$1001=1),
(thatlongstring...!$b$1:$c$1001))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

========
Ps. Watch your ranges--they didn't match!

$A$3:$A$1001
$C$3:$C$1127

Not sure which should be used.


JoFo wrote:
>
> I'm starting a new thread, as I'm not sure if people have seen my reply
> in the old thread - please let me know if this is a problem!
>
> New day - new problem!
>
> My manager has now asked for a SUMIF summary to be on a separate
> spreadsheet (so that she can look at it along with other summaries)
>
> Unfortunately, using this formula:
> =SUMIF('S:\Internal\Sales Figures\2006-2007\[ES 06-07
> Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:\Internal\Sales
> Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127)
>
> just brings up a #Value! unless the spreadsheet it is linking to is
> open. If the linked to spreadsheet is opened, then closed, then I get
> the figures untill I close the summary spreadsheet. Then, when I reopen
> the summary spreadsheet, and click on Update (which I would need to if
> any of the other sections that this spreadsheet links to had been
> updated), I get #Value! again.
>
> I can't see why this doesn't work when the more simple formulae (eg a
> straight ='S:\Internal\Sales Figures\2006-2007\[ES 06-07
> Spreadsheet.xls]Summary'!$B$21) do. What am I doing wrong?
>
> --
> JoFo
> ------------------------------------------------------------------------
> JoFo's Profile: http://www.excelforum.com/member.php...o&userid=36481
> View this thread: http://www.excelforum.com/showthread...hreadid=562786

--

Dave Peterson

[JoFo]

Thanks - I'll take a look at those links. (poor brain isn't working very well with this heat...)

The ranges don't match because I did a scroll down when selecting them - we are not anticipating getting to 1000 visits or quotes over the year!

I have a feeling that the only way I can do this (as I have to be able to look at the whole column) is to use the sumif within the individual workbooks, and then do an = across to the summary spreadsheet.