find the same entries and calculate sum
 

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

find the same entries and calculate sum
 

=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

find the same entries and calculate sum
 

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

find the same entries and calculate sum
 

Sorry: I misread your question.

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

Seems like I can't help!

--
AP

"Alex" a écrit dans le message de
...
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

find the same entries and calculate sum
 

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

find the same entries and calculate sum
 

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

"Ardus Petus" wrote:

Sorry: I misread your question.

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

Seems like I can't help!

--
AP

"Alex" a écrit dans le message de
...
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

find the same entries and calculate sum
 

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

find the same entries and calculate sum
 

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

find the same entries and calculate sum
 

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