I found another formula that is working.
{=SUM(('C:\Path\[Workbook.xls]Sheet1'!$A5:$A99=A3)*'C:\Path\[Workbook.xls]Sheet1'!$B5:$B99)}
Where A3 = "B #2" or "B #3", ...
But, I don't know how to substitute A3 with the multiple option. I was
trying insert &"B*"& instead of A3 in the formula - it's not working.

"Dave Peterson" wrote:

There are some worksheet functions that don't work with closed workbooks.

=indirect(), =sumif(), =countif() are a few.

But maybe you could use a different formula:

=SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99,A3),
('C:\yourfolder\[book1.xls]Sheet1'!$B1:$B99))

Adjust the range to match--but you can't use the whole column.

If you create the formula with the book1.xls workbook open, you may find it
easier. Excel will adjust the formula when you close that workbook.

Alex wrote:

Thank you very much Ardus. It's working very well with opened (looked at)
workbook. But, as I said this searched workbook is closed and from the sheet
where is this formula I'm getting #VALUE! in the cell.

Is it possible to use it with the closed searched workbook?

Thanks

"Ardus Petus" wrote:

=SUMIF(A1:A99,"B*",B1:B99)

HTH
--
AP

"Alex" a écrit dans le message de
...
I have the list of equipment in the column A and amounts in the columnB in
the closed book as follow:
ColumnA ColumnB
F 10
B #1 20
C 10
B #2 15
D 20
...
on the another book I need to build some formula to get the sum of all "B"
(dosn't matter whether it's "B #1" or "B #2").

Can anybody help me with this?

Thanks





--

Dave Peterson