help with SUMIF

[massi]

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

[Dave Peterson]

=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

[Bob Phillips]

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
>

[massi]

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

[Bob Phillips]

=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
>

[massi]

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

[Bob Phillips]

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
>

[massi]

the multiple columns at the end are just two because columns from S to AE are merged as well as AF to AH.
is this going to make things easier?

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

by looking at the formula you sent me earlier i think mine should be ok.
as the columns are merged i think i need to take just the first one to get my data (in this case the column S and the column AF).

[massi]

i doesn't seem to work. instead of the values i get the number of rows that contain the item i want to take in consideration multiplied by 3.... there must be something wrong...
the more i look at it the more i get confused....

[Bob Phillips]

I think that is correct.

--

HTH

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


"massi" <[email protected]> wrote in
message news:[email protected]...
>
> the multiple columns at the end are just two because columns from S to
> AE are merged as well as AF to AH.
> is this going to make things easier?
>
>
=SUMPRODUCT(('P:\mypath\2005\[ESP-2005-10-31.xls]Lab-Chem'!$S$74:$S$95=$B$3)
+('P:\mypath\2005\[ESP-2005-10-31.xls]Lab-Chem'!$AF$74:$AF$95=$B$3)*('P:\myp
ath\2005\[ESP-2005-10-31.xls]Lab-Chem'!$AF$74:$AH$95))
>
> by looking at the formula you sent me earlier i think mine should be
> ok.
> as the columns are merged i think i need to take just the first one to
> get my data (in this case the column S and the column AF).
>
>
> --
> massi
> ------------------------------------------------------------------------
> massi's Profile:
http://www.excelforum.com/member.php...o&userid=29202
> View this thread: http://www.excelforum.com/showthread...hreadid=489446
>

[Bob Phillips]

Mail me the workbooks, I will sort it for you.

--

HTH

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


"massi" <[email protected]> wrote in
message news:[email protected]...
>
> i doesn't seem to work. instead of the values i get the number of rows
> that contain the item i want to take in consideration multiplied by
> 3.... there must be something wrong...
> the more i look at it the more i get confused....
>
>
> --
> massi
> ------------------------------------------------------------------------
> massi's Profile:
http://www.excelforum.com/member.php...o&userid=29202
> View this thread: http://www.excelforum.com/showthread...hreadid=489446
>

[massi]

can't see your email address....

[Bob Phillips]

bob dot phillips at tiscali dot co dot uk

--

HTH

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


"massi" <[email protected]> wrote in
message news:[email protected]...
>
> can't see your email address....
>
>
> --
> massi
> ------------------------------------------------------------------------
> massi's Profile:
http://www.excelforum.com/member.php...o&userid=29202
> View this thread: http://www.excelforum.com/showthread...hreadid=489446
>

[massi]

Hi Bob,
yesterday evening i sent you the files. did you receive them?
Rgds

Massi