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


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

help with SUMIF
 

=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

help with SUMIF
 

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

help with SUMIF
 

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

help with SUMIF
 

=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

help with SUMIF
 

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

help with SUMIF
 

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

help with SUMIF
 

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
------------------------------------------------------------------------
massi's Profile: http://www.excelforum.com/member.php...o&userid=29202
View this thread: http://www.excelforum.com/showthread...hreadid=489446

help with SUMIF
 

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

help with SUMIF
 

I think that is correct.

--

HTH

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


"massi" wrote in
message ...

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

help with SUMIF
 

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

--

HTH

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


"massi" wrote in
message ...

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

help with SUMIF
 

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

help with SUMIF
 

bob dot phillips at tiscali dot co dot uk

--

HTH

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


"massi" wrote in
message ...

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

help with SUMIF
 

Hi Bob,
yesterday evening i sent you the files. did you receive them?
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