|
SumIF linked to another spreadsheet
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. |
SumIF linked to another spreadsheet
Use SUMPRODUCT
=SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ChrisN" wrote in message ... 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. |
SumIF linked to another spreadsheet
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. "Bob Phillips" wrote: Use SUMPRODUCT =SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ChrisN" wrote in message ... 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. |
SumIF linked to another spreadsheet
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) "ChrisN" wrote in message ... 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. "Bob Phillips" wrote: Use SUMPRODUCT =SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ChrisN" wrote in message ... 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. |
SumIF linked to another spreadsheet
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\Account ing\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" wrote: 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) "ChrisN" wrote in message ... 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. "Bob Phillips" wrote: Use SUMPRODUCT =SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ChrisN" wrote in message ... 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. |
SumIF linked to another spreadsheet
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" wrote in message ... 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\Account ing\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" wrote: 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) "ChrisN" wrote in message ... 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. "Bob Phillips" wrote: Use SUMPRODUCT =SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ChrisN" wrote in message ... 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. |
SumIF linked to another spreadsheet
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. ChrisN wrote: 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\Account ing\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" wrote: 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) "ChrisN" wrote in message ... 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. "Bob Phillips" wrote: Use SUMPRODUCT =SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ChrisN" wrote in message ... 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. -- Dave Peterson |
SumIF linked to another spreadsheet
That is exactly how I did it <vbg
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave Peterson" wrote in message ... 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. ChrisN wrote: 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\Account ing\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" wrote: 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) "ChrisN" wrote in message ... 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. "Bob Phillips" wrote: Use SUMPRODUCT =SUMPRODUCT(--(rng_to_test="test_value"),rng_to_sum) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ChrisN" wrote in message ... 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. -- Dave Peterson |
| All times are GMT +1. The time now is 10:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com