sumif, multiple criteria

[smartgal]

I am trying to get the following formula to work:

=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),--([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!!

 

[Pete_UK]

Your first term should be this:

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

Also, you have two open brackets after SUMPRODUCT.

Hope this helps.

Pete

 

[smartgal]

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:$K>0),--([Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

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

Your first term should be this:

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

Also, you have two open brackets after SUMPRODUCT.

Hope this helps.

Pete

I am trying to get the following formula to work:

=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),-­-([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]

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:$K>0),
--([Detail.xls]sales!$K:$K<31),
[Detail.xls]sales!$N:$N)

or

=SUMPRODUCT--([Detail.xls]sales!$B1:$B1111=A2),
--([Detail.xls]sales!$K1:$K1111>0),
--([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).

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:$K>0),--([Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

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

Your first term should be this:

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

Also, you have two open brackets after SUMPRODUCT.

Hope this helps.

Pete

I am trying to get the following formula to work:

=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),-­-([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!!

 

[Pete_UK]

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

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:$K>0),--(­[Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

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

Your first term should be this:

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

Also, you have two open brackets after SUMPRODUCT.

Hope this helps.

Pete

I am trying to get the following formula to work:
=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),-­­-([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 -

 

[smartgal]

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?

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:$K>0),
--([Detail.xls]sales!$K:$K<31),
[Detail.xls]sales!$N:$N)

or

=SUMPRODUCT--([Detail.xls]sales!$B1:$B1111=A2),
--([Detail.xls]sales!$K1:$K1111>0),
--([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).

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:$K>0),--([Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

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

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 <[email protected]>
wrote:
I am trying to get the following formula to work:

=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),-­-([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!!

 

[smartgal]

The formula stands now as:

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

So is it still missing any brackets or parentheses?

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

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:$K>0),--(­[Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

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

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 <[email protected]>
wrote:
I am trying to get the following formula to work:

=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),-­­-([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 -

 

[Pete_UK]

Yes, do it like this:

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

Hope this helps.

Pete

The formula stands now as:

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

So is it still missing any brackets or parentheses?

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

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:$K>0),--(­­[Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)
It is highlighting the final "$N" in the formula as the error
:
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 <[email protected]>
wrote:
I am trying to get the following formula to work:
=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),-­­­-([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 calculateif 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 -

 

[Dave Peterson]

=SUMPRODUCT(--([Detail.xls]sales!$B1:$B1111=A2),
--([Detail.xls]sales!$K1:$K1111>0),
--([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.)

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?

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:$K>0),
--([Detail.xls]sales!$K:$K<31),
[Detail.xls]sales!$N:$N)

or

=SUMPRODUCT--([Detail.xls]sales!$B1:$B1111=A2),
--([Detail.xls]sales!$K1:$K1111>0),
--([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).

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:$K>0),--([Detail.xls]sales!$K:$K<31),[Detail.xls]sales!$N:$N)

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

:

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 <[email protected]>
wrote:
I am trying to get the following formula to work:

=SUMPRODUCT((--([Detail.xls]sales!$B:$B,A2),--([Detail.xls]sales!$K:$K>0),-­-([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!!