sumif

[John Persico]

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to work.

Do I have to use VLOOKUP instead?

--

 

[Dave Peterson]

There are some functions that only work if the sending workbook is open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post easier to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated with first
match and only match), then you could use =vlookup().

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to work.

Do I have to use VLOOKUP instead?

--

 

[John Persico]

Can you tell me what's wrong with my VLOOKUP? The cell shows up as #N/A.

=VLOOKUP(C401,dbl_approved_items.xlsx!A:B,2,FALSE)

My sumif works okay, but I have to have two files open to get it to work,
which I don't want to do.

=SUMIF(dbl_approved_items.xlsx!A:A,C402, dbl_approved_items.xlsx!B:B)

--

There are some functions that only work if the sending workbook is open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post easier
to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated with
first
match and only match), then you could use =vlookup().

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to
work.

Do I have to use VLOOKUP instead?

--

 

[Dave Peterson]

My sumif works okay, but I have to have two files open to get it to work,

That's why I suggested that you try the =sumproduct() formula.

But your syntax for the =vlookup() is wrong for the way you refer to a
workbook. And you didn't include the sheet name for that range, either.

Open both workbooks.

Start writing your formula:
=vlookup(c401,
Now use the mouse to change the window to the dbl_approved_items.xlsx workbook.
Change to the correct worksheet and select those two columns A:B.

Then continue typing the rest of the formula:
,2,false)

Let excel do as much of the heavy lifting as you can. And when you close that
dbl_approved_items.xlsx workbook, excel will even know how to adjust the syntax
to include the drive/path.

Can you tell me what's wrong with my VLOOKUP? The cell shows up as #N/A.

=VLOOKUP(C401,dbl_approved_items.xlsx!A:B,2,FALSE)

My sumif works okay, but I have to have two files open to get it to work,
which I don't want to do.

=SUMIF(dbl_approved_items.xlsx!A:A,C402, dbl_approved_items.xlsx!B:B)

--

There are some functions that only work if the sending workbook is open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post easier
to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated with
first
match and only match), then you could use =vlookup().

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to
work.

Do I have to use VLOOKUP instead?

--

 

[John Persico]

When I use:
=SUMPRODUCT(--(dbl_approved_items.xlsx!$A:$A=C401),(dbl_approved_items.xlsx!$B:$B))

I get a 0 in the cell.
So, it's not working.

When I use:
=SUMIF(dbl_approved_items.xlsx!A:A,C403, dbl_approved_items.xlsx!B:B)

I get a 7.41 in the cell.
So, it IS working.

But, once again I don't want to happen to open up the file name
dbl_appoved_items.xlsx.

So, why is the SUMPRODUCT not working the way I intend it to work? Is there
something wrong with the formula?

Now, when I use:
=VLOOKUP(C401,dbl_approved_items.xlsx!$A$2:$B$25000,2,FALSE)

I get a #N/A in the cell.

So, the only formula that seems to be working here is Sumif, but it requires
me to open up two files.

If sumif works, but the VLOOKUP and SUMPRODUCT don't work, then I must have
the syntax wrong, right?
How can I correct it?

--

There are some functions that only work if the sending workbook is open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post easier
to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated with
first
match and only match), then you could use =vlookup().

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to
work.

Do I have to use VLOOKUP instead?

--

 

[Dave Peterson]

Don't type the ranges yourself.

Use the point and click method that I described in the earlier post.

When I use:
=SUMPRODUCT(--(dbl_approved_items.xlsx!$A:$A=C401),(dbl_approved_items.xlsx!$B:$B))

I get a 0 in the cell.
So, it's not working.

When I use:
=SUMIF(dbl_approved_items.xlsx!A:A,C403, dbl_approved_items.xlsx!B:B)

I get a 7.41 in the cell.
So, it IS working.

But, once again I don't want to happen to open up the file name
dbl_appoved_items.xlsx.

So, why is the SUMPRODUCT not working the way I intend it to work? Is there
something wrong with the formula?

Now, when I use:
=VLOOKUP(C401,dbl_approved_items.xlsx!$A$2:$B$25000,2,FALSE)

I get a #N/A in the cell.

So, the only formula that seems to be working here is Sumif, but it requires
me to open up two files.

If sumif works, but the VLOOKUP and SUMPRODUCT don't work, then I must have
the syntax wrong, right?
How can I correct it?

--

There are some functions that only work if the sending workbook is open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post easier
to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated with
first
match and only match), then you could use =vlookup().

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to
work.

Do I have to use VLOOKUP instead?

--

 

[John Persico]

I did the use the point and click method.
I didn't type any file names or ranges.

--

Don't type the ranges yourself.

Use the point and click method that I described in the earlier post.

When I use:
=SUMPRODUCT(--(dbl_approved_items.xlsx!$A:$A=C401),(dbl_approved_items.xlsx!$B:$B))

I get a 0 in the cell.
So, it's not working.

When I use:
=SUMIF(dbl_approved_items.xlsx!A:A,C403, dbl_approved_items.xlsx!B:B)

I get a 7.41 in the cell.
So, it IS working.

But, once again I don't want to happen to open up the file name
dbl_appoved_items.xlsx.

So, why is the SUMPRODUCT not working the way I intend it to work? Is
there
something wrong with the formula?

Now, when I use:
=VLOOKUP(C401,dbl_approved_items.xlsx!$A$2:$B$25000,2,FALSE)

I get a #N/A in the cell.

So, the only formula that seems to be working here is Sumif, but it
requires
me to open up two files.

If sumif works, but the VLOOKUP and SUMPRODUCT don't work, then I must
have
the syntax wrong, right?
How can I correct it?

--

There are some functions that only work if the sending workbook is
open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post
easier
to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated
with
first
match and only match), then you could use =vlookup().



John Persico wrote:

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to
work.

Do I have to use VLOOKUP instead?

 

[Dave Peterson]

And it created a formula that looked like this:

=SUMPRODUCT(--(dbl_approved_items.xlsx!$A:$A=C401),(dbl_approved_items.xlsx!$B:$B))

I find that difficult to believe.

I would have expected the workbook name surrounded with []'s and the worksheet
name included, too.

I did the use the point and click method.
I didn't type any file names or ranges.

--

Don't type the ranges yourself.

Use the point and click method that I described in the earlier post.

When I use:
=SUMPRODUCT(--(dbl_approved_items.xlsx!$A:$A=C401),(dbl_approved_items.xlsx!$B:$B))

I get a 0 in the cell.
So, it's not working.

When I use:
=SUMIF(dbl_approved_items.xlsx!A:A,C403, dbl_approved_items.xlsx!B:B)

I get a 7.41 in the cell.
So, it IS working.

But, once again I don't want to happen to open up the file name
dbl_appoved_items.xlsx.

So, why is the SUMPRODUCT not working the way I intend it to work? Is
there
something wrong with the formula?

Now, when I use:
=VLOOKUP(C401,dbl_approved_items.xlsx!$A$2:$B$25000,2,FALSE)

I get a #N/A in the cell.

So, the only formula that seems to be working here is Sumif, but it
requires
me to open up two files.

If sumif works, but the VLOOKUP and SUMPRODUCT don't work, then I must
have
the syntax wrong, right?
How can I correct it?

--

There are some functions that only work if the sending workbook is
open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post
easier
to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated
with
first
match and only match), then you could use =vlookup().



John Persico wrote:

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to
work.

Do I have to use VLOOKUP instead?

 

[Dave Peterson]

ps.

Open both workbooks.

Go to any old cell on the sending worksheet in the sending workbook.
Edit|copy (or just ctrl-c)

Then off to the receiving worksheet in the receiving workbook.
Paste Special|Paste link

And you'll see what the syntax is for a simple formula that refers to a cell in
a different workbook.

And it created a formula that looked like this:

=SUMPRODUCT(--(dbl_approved_items.xlsx!$A:$A=C401),(dbl_approved_items.xlsx!$B:$B))

I find that difficult to believe.

I would have expected the workbook name surrounded with []'s and the worksheet
name included, too.

I did the use the point and click method.
I didn't type any file names or ranges.

--

Don't type the ranges yourself.

Use the point and click method that I described in the earlier post.

John Persico wrote:

When I use:
=SUMPRODUCT(--(dbl_approved_items.xlsx!$A:$A=C401),(dbl_approved_items.xlsx!$B:$B))

I get a 0 in the cell.
So, it's not working.

When I use:
=SUMIF(dbl_approved_items.xlsx!A:A,C403, dbl_approved_items.xlsx!B:B)

I get a 7.41 in the cell.
So, it IS working.

But, once again I don't want to happen to open up the file name
dbl_appoved_items.xlsx.

So, why is the SUMPRODUCT not working the way I intend it to work? Is
there
something wrong with the formula?

Now, when I use:
=VLOOKUP(C401,dbl_approved_items.xlsx!$A$2:$B$25000,2,FALSE)

I get a #N/A in the cell.

So, the only formula that seems to be working here is Sumif, but it
requires
me to open up two files.

If sumif works, but the VLOOKUP and SUMPRODUCT don't work, then I must
have
the syntax wrong, right?
How can I correct it?

--

There are some functions that only work if the sending workbook is
open.
=Sumif(), =countif(), =indirect().

But there are alternatives.

=SUMproduct(--('C:\yourlongpath\[wkbkname.xlsx]sheetname'!A:A=C401),
('C:\yourlongpath\[wkbkname.xlsx]sheetname'!b:b))

I substituted short names for your long names just to make the post
easier
to
read (avoiding line wrap issues).

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============
And if you were bringing back a single number (the value associated
with
first
match and only match), then you could use =vlookup().



John Persico wrote:

I have a formula:

=SUMIF('C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401,
'C:\Users\John\Documents\Miscellaneous
Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B)

in a file called product.xlsx.

But, the cell only works when I have dbl_approved_items.xlsx OPEN.

If only the product.xlsx file is open, then I get #VALUE! in the cell.

Why?

I don't want to have to open two files in order to get my formula to
work.

Do I have to use VLOOKUP instead?