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

Thank you very much, Dave. I'm trying to apply it. But, probably I'm getting
it wrong.
As I said in the Column A I have product codes with "A #1","B #1", "B #2",
..... In the Column B I have amounts. I want to get the sum of amounts for all
"B" - dosn't matter if it's #1 or #2.
I'm trying to substitute A3 in your formula with "B*" but it's not working.
Could you please clarify as well what's a role of the '--' in the formula?

Thanks

"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

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

The array formula:

=sum(if(...))

would work on closed files, too.



Alex wrote:

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


--

Dave Peterson