Re: sumproduct-condition as a range

Use this method

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


adapt it to your ranges and *** 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" <amh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1E1DBD5-B2D6-4539-88E1-3181A9D66C25@xxxxxxxxxxxxxxxx
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





.