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.

 

[Bob Phillips]

Massi,

You could use SUMPRODUCT

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

--

HTH

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

 

[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]

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]

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)

 

[Bob Phillips]

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

--

HTH

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

 

[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]

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

Massi