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

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

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

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

You still need one open bracket after SUMPRODUCT and before the first
--

I assumed that you were using XL2007, but if you are not then you
can't use full-column references, as Dave points out.

Hope this helps.

Pete

On Sep 2, 3:13*pm, 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!!- Hide quoted text -

- Show quoted text -

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

The formula stands now as:

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

So is it still missing any brackets or parentheses?

"Pete_UK" wrote:

You still need one open bracket after SUMPRODUCT and before the first
--

I assumed that you were using XL2007, but if you are not then you
can't use full-column references, as Dave points out.

Hope this helps.

Pete

On Sep 2, 3:13 pm, 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!!- Hide quoted text -

- Show quoted text -

Yes, do it like this:

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

Hope this helps.

Pete

On Sep 2, 6:25*pm, smartgal
wrote:
The formula stands now as:

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

So is it still missing any brackets or parentheses?



"Pete_UK" wrote:
You still need one open bracket after SUMPRODUCT and before the first
--

I assumed that you were using XL2007, but if you are not then you
can't use full-column references, as Dave points out.

Hope this helps.

Pete

On Sep 2, 3:13 pm, 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!!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

=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