find the same entries and calculate sum

[Guest]

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

 

[Ardus Petus]

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

HTH

 

[Guest]

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]

Sorry: I misread your question.

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

Seems like I can't help!

 

[Dave Peterson]

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.

 

[Guest]

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

 

[Guest]

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

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.

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

 

[Guest]

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.

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.

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

 

[Dave Peterson]

The array formula:

=sum(if(...))

would work on closed files, too.

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.

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.

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

:

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

HTH
--
AP

"Alex" <[email protected]> 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