help with SUMIF

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

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>