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