Re: sumproduct-condition as a range

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" <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






.

Relevant Pages

  • Re: sumproduct-condition as a range
    ... A are account numbers, B are sub account ... On another tab, I have a range of sub ... If I only reference one cell in my second condition, ...
    (microsoft.public.excel.misc)
  • Re: sumproduct-condition as a range
    ... A are account numbers, B are sub account ... On another tab, I have a range of sub ... If I only reference one cell in my second condition, ...
    (microsoft.public.excel.misc)
  • Re: Windows Media player(transfering files between user accnts.)
    ... file ownership and permissions supersede administrator rights. ... This is not your administrator account, ... >> Open Explorer, go to Tools and Folder Options, on the view tab, scroll to ...
    (microsoft.public.windowsxp.basics)
  • Re: New Disk File Problem
    ... This is not your administrator account, ... Open Explorer, go to Tools and Folder Options, on the view tab, scroll to ... > I have gone to windows explorer tools and selected folder options and the> view tab. ...
    (microsoft.public.windowsxp.configuration_manage)
  • Re: New Disk File Problem
    ... This is not your administrator account, ... Open Explorer, go to Tools and Folder Options, on the view tab, scroll to ... > I have gone to windows explorer tools and selected folder options and the> view tab. ...
    (microsoft.public.windowsxp.customize)