=SUMPRODUCT(--([Detail.xls]sales!$B1:$B1111=A2),
--([Detail.xls]sales!$K1:$K11110),
--([Detail.xls]sales!$K1:$K1111<31),
[Detail.xls]sales!$N1:$N1111)

But since you're using xl2007, you can use the entire column.

(I missed the leading open paren when I copied your existing formula.)

smartgal wrote:

This is so frustrating! I made all the changes on my formula (had to do a
date range because I'm on 2007) and it still didn't work. So then I copied
YOUR formula, just to see, and it STILL didn't work - it continues to
highlight the "$N1111" (the very last one) in the formula. Why doesn't it
like this formula?

"Dave Peterson" wrote:

Are you using xl2007, if no then you can't use the whole column in any of those
ranges:

=SUMPRODUCT--([Detail.xls]sales!$B:$B=A2),
--([Detail.xls]sales!$K:$K0),
--([Detail.xls]sales!$K:$K<31),
[Detail.xls]sales!$N:$N)

or

=SUMPRODUCT--([Detail.xls]sales!$B1:$B1111=A2),
--([Detail.xls]sales!$K1:$K11110),
--([Detail.xls]sales!$K1:$K1111<31),
[Detail.xls]sales!$N1:$N1111)

(change the range to be big enough--but not too big.)

Notice that the first comparison has been changed (comma became an equal sign).



smartgal wrote:

Okay, fixed as you suggested (including the removal of the two leading open
parens), I now have this:

=SUMPRODUCT--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K0),--([Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

It is highlighting the final "$N" in the formula as the error

"Pete_UK" wrote:

Your first term should be this:

--([Detail.xls]sales!$B:$B=A2),

Also, you have two open brackets after SUMPRODUCT.

Hope this helps.

Pete

On Sep 2, 2:45 pm, smartgal
wrote:
I am trying to get the following formula to work:

=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K0),-ÂÂ*-([Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

On the "sales" tab col B is a salesperson, Col K is a number value and
column N is also a number value and is what I'd like to calculate if the
conditions identified for A and K match . . .

Column K is the kicker - the conditions I'm seeking for Column K is that it
be greater than zero but less than 31. What's wrong with the formula above?

Thanks!!



--

Dave Peterson


--

Dave Peterson