MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Question sumif vs vlookup


Posted by JC on September 27, 2001 2:00 PM

Some Please help me.

This formula will not work, unless I open the file named Stock.

=SUMIF('\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$A:A,$B25,'\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$C:C)


This formula does work without having to open the file Stock.

=IF($B28<>"",VLOOKUP($B25,'\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$1:$100,3,0),"")

I would like to use the first formula because I need the ability to use WildCards “*” I do not understand why it does not work with the SUMIF function.

Thanks,


J.C.


Posted by Mark W. on September 28, 2001 5:56 AM

Check your references...

\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$A:A
'\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$C:C

are mixed references. Try changing them to
absolute references as follows...

'\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$A:$A
'\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$C:$C

Posted by J.C, on September 28, 2001 7:28 AM

Re: Check your references..., I did and it still #VALUE error

I tried but it still will not work. Unless I open the file. '\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$A:A


Posted by Mark W. on September 28, 2001 10:11 AM

Does...

\\SERVER4\SYS\SALES\[STOCK.XLS]Stock'!$A:$A
contain any #VALUE! errors?