ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   sumif, multiple criteria (https://www.excelbanter.com/excel-discussion-misc-queries/241429-sumif-multiple-criteria.html)

smartgal
sumif, multiple criteria
 
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!!

Pete_UK
sumif, multiple criteria
 
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!!

smartgal
sumif, multiple criteria
 
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
sumif, multiple criteria
 
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

Pete_UK
sumif, multiple criteria
 
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 -

smartgal
sumif, multiple criteria
 
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

smartgal
sumif, multiple criteria
 
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 -

Pete_UK
sumif, multiple criteria
 
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 -

Dave Peterson
sumif, multiple criteria
 
=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


All times are GMT +1. The time now is 02:09 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com