Hello,

I'm having trouble with a formula using sumproduct. I've been using
http://www.xldynamic.com/source/xld.SUMPRODUCT.html as a guide, and it's been
great. Everything works fine when I use a single criteria or single cell
reference in the conditions in the forumlas, but I want to use a range in my
second condition, and am getting #Value as a return. I have queried 3
columns A, B, and C on one tab. A are account numbers, B are sub account
numbers, and C are amounts. On another tab, I have a range of sub accounts
that I want excluded from my results. I can't seem to get this set up
properly. So far I have

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <A8:A37),('Allocable Costs'!$C$2:$C$2000))

which returns #N/A. If I only reference one cell in my second condition, it
works fine, but with the range of <A8:A37, I'm getting an error. From the
website above I found example 3 at the bottom of the page and tried

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <TRANSPOSE(A8:A37)),('Allocable Costs'!$C$2:$C$2000))

but that returns #Value. Am I using this incorrectly? I'm assuming I need
to use the Transpose because my criteria is in a column and not a row, but am
at a loss. Any guidance appreciated.

Thanks,
AMH

Use this method

=SUMPRODUCT(--(A2:A100=B56),--(ISNA(MATCH(B2:B100,D8:D37,0))),C2:C100)


adapt it to your ranges and sheet names, meaning that the B2:B2000 part
should look like


,--(ISNA(MATCH('Allocable Costs'!$B$2:$B$2000,A8:A37,0))),


I tested it and it works


--
Regards,

Peo Sjoblom


"amh" wrote in message
...
Hello,

I'm having trouble with a formula using sumproduct. I've been using
http://www.xldynamic.com/source/xld.SUMPRODUCT.html as a guide, and it's
been
great. Everything works fine when I use a single criteria or single cell
reference in the conditions in the forumlas, but I want to use a range in
my
second condition, and am getting #Value as a return. I have queried 3
columns A, B, and C on one tab. A are account numbers, B are sub account
numbers, and C are amounts. On another tab, I have a range of sub
accounts
that I want excluded from my results. I can't seem to get this set up
properly. So far I have

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <A8:A37),('Allocable Costs'!$C$2:$C$2000))

which returns #N/A. If I only reference one cell in my second condition,
it
works fine, but with the range of <A8:A37, I'm getting an error. From
the
website above I found example 3 at the bottom of the page and tried

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <TRANSPOSE(A8:A37)),('Allocable Costs'!$C$2:$C$2000))

but that returns #Value. Am I using this incorrectly? I'm assuming I
need
to use the Transpose because my criteria is in a column and not a row, but
am
at a loss. Any guidance appreciated.

Thanks,
AMH

I think there is an example of that, but perhaps it is not clear as to when
to use

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),
--(NOT(ISNUMBER(MATCH('Allocable
Costs'!$B$2:$B$2000,A8:A37,0)))),('Allocable Costs'!$C$2:$C$2000))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"amh" wrote in message
...
Hello,

I'm having trouble with a formula using sumproduct. I've been using
http://www.xldynamic.com/source/xld.SUMPRODUCT.html as a guide, and it's
been
great. Everything works fine when I use a single criteria or single cell
reference in the conditions in the forumlas, but I want to use a range in
my
second condition, and am getting #Value as a return. I have queried 3
columns A, B, and C on one tab. A are account numbers, B are sub account
numbers, and C are amounts. On another tab, I have a range of sub
accounts
that I want excluded from my results. I can't seem to get this set up
properly. So far I have

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <A8:A37),('Allocable Costs'!$C$2:$C$2000))

which returns #N/A. If I only reference one cell in my second condition,
it
works fine, but with the range of <A8:A37, I'm getting an error. From
the
website above I found example 3 at the bottom of the page and tried

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <TRANSPOSE(A8:A37)),('Allocable Costs'!$C$2:$C$2000))

but that returns #Value. Am I using this incorrectly? I'm assuming I
need
to use the Transpose because my criteria is in a column and not a row, but
am
at a loss. Any guidance appreciated.

Thanks,
AMH

Thanks to both of you. Both ways worked perfectly ! I really appreciate the
help.

AMH

"Bob Phillips" wrote:

I think there is an example of that, but perhaps it is not clear as to when
to use

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),
--(NOT(ISNUMBER(MATCH('Allocable
Costs'!$B$2:$B$2000,A8:A37,0)))),('Allocable Costs'!$C$2:$C$2000))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"amh" wrote in message
...
Hello,

I'm having trouble with a formula using sumproduct. I've been using
http://www.xldynamic.com/source/xld.SUMPRODUCT.html as a guide, and it's
been
great. Everything works fine when I use a single criteria or single cell
reference in the conditions in the forumlas, but I want to use a range in
my
second condition, and am getting #Value as a return. I have queried 3
columns A, B, and C on one tab. A are account numbers, B are sub account
numbers, and C are amounts. On another tab, I have a range of sub
accounts
that I want excluded from my results. I can't seem to get this set up
properly. So far I have

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <A8:A37),('Allocable Costs'!$C$2:$C$2000))

which returns #N/A. If I only reference one cell in my second condition,
it
works fine, but with the range of <A8:A37, I'm getting an error. From
the
website above I found example 3 at the bottom of the page and tried

=SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable
Costs'!$B$2:$B$2000 <TRANSPOSE(A8:A37)),('Allocable Costs'!$C$2:$C$2000))

but that returns #Value. Am I using this incorrectly? I'm assuming I
need
to use the Transpose because my criteria is in a column and not a row, but
am
at a loss. Any guidance appreciated.

Thanks,
AMH