| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
| SUMIF Not | Excel Discussion (Misc queries) | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
