Hi there.

I have a spreadsheet (which i will call file A) that by using the
formula SUMIF retrieve some information from an other spreadsheet (file
B) which contains reference to another file (file C).

the formula in file A works fine if file B is open (please note that I
don't have access to file C) but whenever file B is closed it shows the
error "#VALUE!". As I have dozens of files B (one for each day) I cannot
open them all and I was wondering if there is a way to solve my
problem.

I hope I was clear enough...

Rgds

Massi


--
massi
------------------------------------------------------------------------
massi's Profile: http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446

=sumif() is one of those formulas that don't work nicely with closed workbooks.

But there are alternatives. You could use =sumproduct().

This is an example from a different question:

=sumproduct(--('yourpath\[wkbk1.xls]sheet1!B10:B100=c2),
('yourpath\[wkbk1.xls]sheet1!F10:F100))

You can't use the whole column in this formula, though.


massi wrote:

Hi there.

I have a spreadsheet (which i will call file A) that by using the
formula SUMIF retrieve some information from an other spreadsheet (file
B) which contains reference to another file (file C).

the formula in file A works fine if file B is open (please note that I
don't have access to file C) but whenever file B is closed it shows the
error "#VALUE!". As I have dozens of files B (one for each day) I cannot
open them all and I was wondering if there is a way to solve my
problem.

I hope I was clear enough...

Rgds

Massi

--
massi
------------------------------------------------------------------------
massi's Profile: http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446

--

Dave Peterson

Massi,

You could use SUMPRODUCT

=SUMPRODUCT(--(rng1="value"),rng2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"massi" wrote in
message ...

Hi there.

I have a spreadsheet (which i will call file A) that by using the
formula SUMIF retrieve some information from an other spreadsheet (file
B) which contains reference to another file (file C).

the formula in file A works fine if file B is open (please note that I
don't have access to file C) but whenever file B is closed it shows the
error "#VALUE!". As I have dozens of files B (one for each day) I cannot
open them all and I was wondering if there is a way to solve my
problem.

I hope I was clear enough...

Rgds

Massi


--
massi
------------------------------------------------------------------------
massi's Profile:
http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446

thanks for your replies.

I have tried to use *sumproduct * but it doesn't work. I don't know if
I made a mistake with the sintax or something else.
the formula is:

=SUMPRODUCT('[ESP-2005-10-31.xls]Lab'!$S$74:$AH$95=$B$3,'[ESP-2005-10-31.xls]Lab'!$AF$74:$AH$95)

the cell B3 is in my spreadsheet, while the other in the formula refers
to another file.

one thing i didn't say on my previous tread: the column with the name
of the items that i want to compare is made of 13 columns merged and
the one next to it where i actually take the values from is made of 3.
I hope this woudn't make everything even more complicated...

Rgds

Massi


--
massi
------------------------------------------------------------------------
massi's Profile: http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446

=SUMPRODUCT(--('[ESP-2005-10-31.xls]Lab'!$S$74:$AH$95=$B$3),'[ESP-2005-10-31
..xls]Lab'!$AF$74:$AH$95)

I don't think the merged columns matter.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"massi" wrote in
message ...

thanks for your replies.

I have tried to use *sumproduct * but it doesn't work. I don't know if
I made a mistake with the sintax or something else.
the formula is:


=SUMPRODUCT('[ESP-2005-10-31.xls]Lab'!$S$74:$AH$95=$B$3,'[ESP-2005-10-31.xls
]Lab'!$AF$74:$AH$95)

the cell B3 is in my spreadsheet, while the other in the formula refers
to another file.

one thing i didn't say on my previous tread: the column with the name
of the items that i want to compare is made of 13 columns merged and
the one next to it where i actually take the values from is made of 3.
I hope this woudn't make everything even more complicated...

Rgds

Massi


--
massi
------------------------------------------------------------------------
massi's Profile:
http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446

still doesn't work..

=SUMPRODUCT(--('P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$S$74:$AH$95=$B$3),'P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$AF$74:$AH$95)

do i need to put the two lines at the beginning of the formula?

does it seem to be ok for you?

M


--
massi
------------------------------------------------------------------------
massi's Profile: http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446

Obviously I don't have your workbooks, but the principle works fine for.
This is the test I ran

=SUMPRODUCT(--('D:\Bob\Consultancy\[Timesheet.xls]20 Nov
2005'!$A$7:$A$15="NGs"),'D:\Bob\Consultancy\[Timesheet.xls]20 Nov
2005'!$C$7:$C$15)

Just noticed that you have multiple columns. That is trickier. I think you
have to test each column individually, like

=SUMPRODUCT((('P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$S$74:$S$95=$B$3)+
('P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$T$74:$T$95=$B$3)+
('P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$U$74:$U$95=$B$3)+
etc.
)*('P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$AF$74:$AH$95))




--

HTH

RP
(remove nothere from the email address if mailing direct)


"massi" wrote in
message ...

still doesn't work..


=SUMPRODUCT(--('P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$S$74:$AH$95=$B$3),'
P:\mypath\2005\[ESP-2005-10-31.xls]Lab'!$AF$74:$AH$95)

do i need to put the two lines at the beginning of the formula?

does it seem to be ok for you?

M


--
massi
------------------------------------------------------------------------
massi's Profile:
http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446