sumif, multiple criteria

S

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

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
 
S

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

Pete_UK said:
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!!
 
D

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

Pete_UK said:
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!!
 
P

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



Pete_UK said:
Your first term should be this:
--([Detail.xls]sales!$B:$B=A2),

Also, you have two open brackets after SUMPRODUCT.
Hope this helps.

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

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?

Dave Peterson said:
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

Pete_UK said:
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!!
 
S

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?

Pete_UK said:
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



Pete_UK said:
Your first term should be this:
--([Detail.xls]sales!$B:$B=A2),

Also, you have two open brackets after SUMPRODUCT.
Hope this helps.

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

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?



Pete_UK said:
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.

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

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?

Dave Peterson said:
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!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top