software

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 quot;Bquot;
(dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).

Can anybody help me with this?

Thanks=SUMIF(A1:A99,quot;B*quot;,B1:B99)

HTH
--
AP

quot;Alexquot; gt; a écrit dans le message de
...
gt; I have the list of equipment in the column A and amounts in the columnB in
gt; the closed book as follow:
gt; ColumnA ColumnB
gt; F 10
gt; B #1 20
gt; C 10
gt; B #2 15
gt; D 20
gt; ...
gt; on the another book I need to build some formula to get the sum of all quot;Bquot;
gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt;
gt; Can anybody help me with this?
gt;
gt; Thanks
gt;
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

quot;Ardus Petusquot; wrote:

gt; =SUMIF(A1:A99,quot;B*quot;,B1:B99)
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Alexquot; gt; a écrit dans le message de
gt; ...
gt; gt; I have the list of equipment in the column A and amounts in the columnB in
gt; gt; the closed book as follow:
gt; gt; ColumnA ColumnB
gt; gt; F 10
gt; gt; B #1 20
gt; gt; C 10
gt; gt; B #2 15
gt; gt; D 20
gt; gt; ...
gt; gt; on the another book I need to build some formula to get the sum of all quot;Bquot;
gt; gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt; gt;
gt; gt; Can anybody help me with this?
gt; gt;
gt; gt; Thanks
gt; gt;
gt;
gt;
gt;

Sorry: I misread your question.

Same by me: getting #VALUE error when WB is closed...

Seems like I can't help!

--
AP

quot;Alexquot; gt; a écrit dans le message de
...
gt; Thank you very much Ardus. It's working very well with opened (looked at)
gt; workbook. But, as I said this searched workbook is closed and from the
sheet
gt; where is this formula I'm getting #VALUE! in the cell.
gt;
gt; Is it possible to use it with the closed searched workbook?
gt;
gt; Thanks
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; =SUMIF(A1:A99,quot;B*quot;,B1:B99)
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;Alexquot; gt; a écrit dans le message de
gt; gt; ...
gt; gt; gt; I have the list of equipment in the column A and amounts in the
columnB in
gt; gt; gt; the closed book as follow:
gt; gt; gt; ColumnA ColumnB
gt; gt; gt; F 10
gt; gt; gt; B #1 20
gt; gt; gt; C 10
gt; gt; gt; B #2 15
gt; gt; gt; D 20
gt; gt; gt; ...
gt; gt; gt; on the another book I need to build some formula to get the sum of all
quot;Bquot;
gt; gt; gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt; gt; gt;
gt; gt; gt; Can anybody help me with this?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
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:
gt;
gt; Thank you very much Ardus. It's working very well with opened (looked at)
gt; workbook. But, as I said this searched workbook is closed and from the sheet
gt; where is this formula I'm getting #VALUE! in the cell.
gt;
gt; Is it possible to use it with the closed searched workbook?
gt;
gt; Thanks
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; =SUMIF(A1:A99,quot;B*quot;,B1:B99)
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;Alexquot; gt; a écrit dans le message de
gt; gt; ...
gt; gt; gt; I have the list of equipment in the column A and amounts in the columnB in
gt; gt; gt; the closed book as follow:
gt; gt; gt; ColumnA ColumnB
gt; gt; gt; F 10
gt; gt; gt; B #1 20
gt; gt; gt; C 10
gt; gt; gt; B #2 15
gt; gt; gt; D 20
gt; gt; gt; ...
gt; gt; gt; on the another book I need to build some formula to get the sum of all quot;Bquot;
gt; gt; gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt; gt; gt;
gt; gt; gt; Can anybody help me with this?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson

Ardus, you helped a lot.
I'm using your formula and doing it thru a program code.

The program in a system (not visible for a user) is openning and closing all
files (around 50) one by one. Before closing (as it's still opened) I'm using
the formula to copy the value in the different column:
If (Not IsNull(xlapp.VLookup(strProdName, rngContin, 10, False))) _
And (Not IsError(xlapp.VLookup(strProdName, rngContin, 10, False))) Then
varContin = xlapp.VLookup(strProdName, rngContin, 10, False)
num = xlapp.Match(strProdName, rngContin.Columns(1), 0)
Set rngOne = rngContin(num, 11)
rngOne.Value = varContin
If (Not IsNull(xlapp.SumIf(rngEquipA, strSpiral, rngEquipB))) _
And (Not IsError(xlapp.SumIf(rngEquipA, strSpiral, rngEquipB))) Then
intSpiralHrs = xlapp.SumIf(rngEquipA, strSpiral, rngEquipB) - 'here
is the formula
Set rngSpiral = rngContin(num, 15)
rngSpiral.Value = intSpiralHrs
End If

So, thanks a lot.
Now, I'm researching Dave's suggestions whether it's possible to substitute
the code with just a formula in a cell.

Best Regards,

Alex

quot;Ardus Petusquot; wrote:

gt; Sorry: I misread your question.
gt;
gt; Same by me: getting #VALUE error when WB is closed...
gt;
gt; Seems like I can't help!
gt;
gt; --
gt; AP
gt;
gt; quot;Alexquot; gt; a écrit dans le message de
gt; ...
gt; gt; Thank you very much Ardus. It's working very well with opened (looked at)
gt; gt; workbook. But, as I said this searched workbook is closed and from the
gt; sheet
gt; gt; where is this formula I'm getting #VALUE! in the cell.
gt; gt;
gt; gt; Is it possible to use it with the closed searched workbook?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt; gt; =SUMIF(A1:A99,quot;B*quot;,B1:B99)
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;Alexquot; gt; a écrit dans le message de
gt; gt; gt; ...
gt; gt; gt; gt; I have the list of equipment in the column A and amounts in the
gt; columnB in
gt; gt; gt; gt; the closed book as follow:
gt; gt; gt; gt; ColumnA ColumnB
gt; gt; gt; gt; F 10
gt; gt; gt; gt; B #1 20
gt; gt; gt; gt; C 10
gt; gt; gt; gt; B #2 15
gt; gt; gt; gt; D 20
gt; gt; gt; gt; ...
gt; gt; gt; gt; on the another book I need to build some formula to get the sum of all
gt; quot;Bquot;
gt; gt; gt; gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt; gt; gt; gt;
gt; gt; gt; gt; Can anybody help me with this?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

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 quot;A #1quot;,quot;B #1quot;, quot;B #2quot;,
..... In the Column B I have amounts. I want to get the sum of amounts for all
quot;Bquot; - dosn't matter if it's #1 or #2.
I'm trying to substitute A3 in your formula with quot;B*quot; but it's not working.
Could you please clarify as well what's a role of the '--' in the formula?

Thanks

quot;Dave Petersonquot; wrote:

gt; There are some worksheet functions that don't work with closed workbooks.
gt;
gt; =indirect(), =sumif(), =countif() are a few.
gt;
gt; But maybe you could use a different formula:
gt;
gt; =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99,A3),
gt; ('C:\yourfolder\[book1.xls]Sheet1'!$B1:$B99))
gt;
gt; Adjust the range to match--but you can't use the whole column.
gt;
gt; If you create the formula with the book1.xls workbook open, you may find it
gt; easier. Excel will adjust the formula when you close that workbook.
gt;
gt; Alex wrote:
gt; gt;
gt; gt; Thank you very much Ardus. It's working very well with opened (looked at)
gt; gt; workbook. But, as I said this searched workbook is closed and from the sheet
gt; gt; where is this formula I'm getting #VALUE! in the cell.
gt; gt;
gt; gt; Is it possible to use it with the closed searched workbook?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt; gt; =SUMIF(A1:A99,quot;B*quot;,B1:B99)
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;Alexquot; gt; a écrit dans le message de
gt; gt; gt; ...
gt; gt; gt; gt; I have the list of equipment in the column A and amounts in the columnB in
gt; gt; gt; gt; the closed book as follow:
gt; gt; gt; gt; ColumnA ColumnB
gt; gt; gt; gt; F 10
gt; gt; gt; gt; B #1 20
gt; gt; gt; gt; C 10
gt; gt; gt; gt; B #2 15
gt; gt; gt; gt; D 20
gt; gt; gt; gt; ...
gt; gt; gt; gt; on the another book I need to build some formula to get the sum of all quot;Bquot;
gt; gt; gt; gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt; gt; gt; gt;
gt; gt; gt; gt; Can anybody help me with this?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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 = quot;B #2quot; or quot;B #3quot;, ...
But, I don't know how to substitute A3 with the multiple option. I was
trying insert amp;quot;B*quot;amp; instead of A3 in the formula - it's not working.

quot;Dave Petersonquot; wrote:

gt; There are some worksheet functions that don't work with closed workbooks.
gt;
gt; =indirect(), =sumif(), =countif() are a few.
gt;
gt; But maybe you could use a different formula:
gt;
gt; =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99,A3),
gt; ('C:\yourfolder\[book1.xls]Sheet1'!$B1:$B99))
gt;
gt; Adjust the range to match--but you can't use the whole column.
gt;
gt; If you create the formula with the book1.xls workbook open, you may find it
gt; easier. Excel will adjust the formula when you close that workbook.
gt;
gt; Alex wrote:
gt; gt;
gt; gt; Thank you very much Ardus. It's working very well with opened (looked at)
gt; gt; workbook. But, as I said this searched workbook is closed and from the sheet
gt; gt; where is this formula I'm getting #VALUE! in the cell.
gt; gt;
gt; gt; Is it possible to use it with the closed searched workbook?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt; gt; =SUMIF(A1:A99,quot;B*quot;,B1:B99)
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;Alexquot; gt; a écrit dans le message de
gt; gt; gt; ...
gt; gt; gt; gt; I have the list of equipment in the column A and amounts in the columnB in
gt; gt; gt; gt; the closed book as follow:
gt; gt; gt; gt; ColumnA ColumnB
gt; gt; gt; gt; F 10
gt; gt; gt; gt; B #1 20
gt; gt; gt; gt; C 10
gt; gt; gt; gt; B #2 15
gt; gt; gt; gt; D 20
gt; gt; gt; gt; ...
gt; gt; gt; gt; on the another book I need to build some formula to get the sum of all quot;Bquot;
gt; gt; gt; gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt; gt; gt; gt;
gt; gt; gt; gt; Can anybody help me with this?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

The array formula:

=sum(if(...))

would work on closed files, too.
Alex wrote:
gt;
gt; I found another formula that is working.
gt; {=SUM(('C:\Path\[Workbook.xls]Sheet1'!$A5:$A99=A3)*'C:\Path\[Workbook.xls]Sheet1'!$B5:$B99)}
gt; Where A3 = quot;B #2quot; or quot;B #3quot;, ...
gt; But, I don't know how to substitute A3 with the multiple option. I was
gt; trying insert amp;quot;B*quot;amp; instead of A3 in the formula - it's not working.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; There are some worksheet functions that don't work with closed workbooks.
gt; gt;
gt; gt; =indirect(), =sumif(), =countif() are a few.
gt; gt;
gt; gt; But maybe you could use a different formula:
gt; gt;
gt; gt; =SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99,A3),
gt; gt; ('C:\yourfolder\[book1.xls]Sheet1'!$B1:$B99))
gt; gt;
gt; gt; Adjust the range to match--but you can't use the whole column.
gt; gt;
gt; gt; If you create the formula with the book1.xls workbook open, you may find it
gt; gt; easier. Excel will adjust the formula when you close that workbook.
gt; gt;
gt; gt; Alex wrote:
gt; gt; gt;
gt; gt; gt; Thank you very much Ardus. It's working very well with opened (looked at)
gt; gt; gt; workbook. But, as I said this searched workbook is closed and from the sheet
gt; gt; gt; where is this formula I'm getting #VALUE! in the cell.
gt; gt; gt;
gt; gt; gt; Is it possible to use it with the closed searched workbook?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; quot;Ardus Petusquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =SUMIF(A1:A99,quot;B*quot;,B1:B99)
gt; gt; gt; gt;
gt; gt; gt; gt; HTH
gt; gt; gt; gt; --
gt; gt; gt; gt; AP
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Alexquot; gt; a écrit dans le message de
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; I have the list of equipment in the column A and amounts in the columnB in
gt; gt; gt; gt; gt; the closed book as follow:
gt; gt; gt; gt; gt; ColumnA ColumnB
gt; gt; gt; gt; gt; F 10
gt; gt; gt; gt; gt; B #1 20
gt; gt; gt; gt; gt; C 10
gt; gt; gt; gt; gt; B #2 15
gt; gt; gt; gt; gt; D 20
gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; on the another book I need to build some formula to get the sum of all quot;Bquot;
gt; gt; gt; gt; gt; (dosn't matter whether it's quot;B #1quot; or quot;B #2quot;).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Can anybody help me with this?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson