SumIF linked to another spreadsheet

C

ChrisN

I have a SumIf linked to another spreadsheet. It works if all spreadsheets
are open, but does not work if the source spreadsheets are not open. How can
I solve for this. I have linked this way before Excel 2003 - but this
version doesn't seem to allow it.
 
B

Bob Phillips

Use SUMPRODUCT

=SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
C

ChrisN

But I am adding cells, not multiplying. I'm not sure how that would help me
add a number in another spreadsheet.

I thought I had done this in previous Excel versions, but not I don't
remember if the sumif was related to data in another spreadsheet or within
the same worksheet.

Is that what it is...that it will not work outside the open spreadsheet? It
is weird that the only time it doesn't work is when the other spreadsheets
are closed. If they are open, it works fine.
 
B

Bob Phillips

SUMIF doesn't work on closed workbooks. Never has AFAIK.

Try my SUMPRODUCT, you may be pleasantly surprised.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
C

ChrisN

I am trying your formula. Does this look right? It still didn't seem to work.

=SUMProduct(--('H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by
IRG_SRG'!$A$8:$I$25=$C$2),'H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by IRG_SRG'!$I$8:$I$25)

Thanks for your help
 
B

Bob Phillips

This should be okay

=SUMPRODUCT(--('H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by IRG_SRG'!$A$8:$A$25=$C$2),
'H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by IRG_SRG'!$I$8:$I$25)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

ChrisN said:
I am trying your formula. Does this look right? It still didn't seem to work.

=SUMProduct(--('H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by
IRG_SRG'!$A$8:$I$25=$C$2),'H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by IRG_SRG'!$I$8:$I$25)

Thanks for your help

Bob Phillips said:
SUMIF doesn't work on closed workbooks. Never has AFAIK.

Try my SUMPRODUCT, you may be pleasantly surprised.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

help
me spreadsheet?
It open.
How
Click to expand...
Click to expand...
 
D

Dave Peterson

When I have trouble with long formulas like this, I open both workbooks. Then I
build the formula by pointing and clicking.

When I close that other workbook, excel will adjust the formula to include the
path.
I am trying your formula. Does this look right? It still didn't seem to work.

=SUMProduct(--('H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by
IRG_SRG'!$A$8:$I$25=$C$2),'H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by IRG_SRG'!$I$8:$I$25)

Thanks for your help

Bob Phillips said:
SUMIF doesn't work on closed workbooks. Never has AFAIK.

Try my SUMPRODUCT, you may be pleasantly surprised.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
Click to expand...
 
B

Bob Phillips

That is exactly how I did it <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Dave Peterson said:
When I have trouble with long formulas like this, I open both workbooks. Then I
build the formula by pointing and clicking.

When I close that other workbook, excel will adjust the formula to include the
path.
I am trying your formula. Does this look right? It still didn't seem to work.

=SUMProduct(--('H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by
IRG_SRG'!$A$8:$I$25=$C$2),'H:\US\Rochester\Accounting\General
Files\Personal\ChristineF\Corporate Finance\Metrics Dashboard\[Sales
Forecast.xls]Forecast by IRG_SRG'!$I$8:$I$25)

Thanks for your help

Bob Phillips said:
SUMIF doesn't work on closed workbooks. Never has AFAIK.

Try my SUMPRODUCT, you may be pleasantly surprised.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

But I am adding cells, not multiplying. I'm not sure how that would help
me
add a number in another spreadsheet.

I thought I had done this in previous Excel versions, but not I don't
remember if the sumif was related to data in another spreadsheet or within
the same worksheet.

Is that what it is...that it will not work outside the open spreadsheet?
It
is weird that the only time it doesn't work is when the other spreadsheets
are closed. If they are open, it works fine.

:

Use SUMPRODUCT

=SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I have a SumIf linked to another spreadsheet. It works if all
spreadsheets
are open, but does not work if the source spreadsheets are not open.
How
can
I solve for this. I have linked this way before Excel 2003 - but this
version doesn't seem to allow it.
Click to expand...
Click to expand...
Click to expand...
 
Top