Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
creativeops
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' statement w

Okay, multipart question that I'm really hoping you wizards can answer...

I'm trying to sum #s into a table from a separate document (the source doc
is on our company intranet, hence the '...' below - that is a lonngg intranet
address). The #s to be summed have to meet a variety of criteria. However,
it often has to meet criteria A or B (or C) in the same column, and similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I make it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a limit to
# of characters
4. Assuming I could make an OR statement work how would I get around the
length problem?
5. Last one! I'd rather just have it search the whole column instead of
specific rows, but when I tried A:A it gave an error. Any way to do that?

On the formulas I do have with less OR possibilities, the formula doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type 1"),--('...'!L2:L65000))
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' statement w

Sticking to only 1 Q per post (that's actually supposed to be the "rule")
will certainly make it more attractive to responders ..

Just some thoughts ..
(doesn't cover all your questions, just some key ones):

AND example, used to check entries in say 2 different cols:
=SUMPRODUCT(--(A1:A10="London"),--(B1:B10="Munich"))

This AND construct however:
=SUMPRODUCT(--(A1:A10="London"),--(A1:A10="Munich"))
is usually not meaningful, as each cell in col A will contain only 1 city
input. So only zero would be returned.

OR example:
=SUMPRODUCT(--(A1:A10={"London","Munich"}))

SUMPRODUCT cannot accept entire col references (eg: A:A).
Keep the ranges eg: A1:A10, to the *smallest* possible extent (for
performance reasons)
Keep sheetnames short and sweet. Use sheetnames like: A, B, C or : 1,2,3 or:
T1,T2,T3 (Benefits: Shortens formula length, easy edit, .. )
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can answer...

I'm trying to sum #s into a table from a separate document (the source doc
is on our company intranet, hence the '...' below - that is a lonngg

intranet
address). The #s to be summed have to meet a variety of criteria.

However,
it often has to meet criteria A or B (or C) in the same column, and

similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I make

it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a limit

to
# of characters
4. Assuming I could make an OR statement work how would I get around the
length problem?
5. Last one! I'd rather just have it search the whole column instead of
specific rows, but when I tried A:A it gave an error. Any way to do that?

On the formulas I do have with less OR possibilities, the formula doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross


=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('..
..'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="Brand
Y"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...
'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' statement w

Assuming you want to use it as OR instead of AND?

=SUMPRODUCT(--((A2:A30="London")+(A2:A30="Munich")0),--((J2:J30="BrandX")+(J2:J30="BrandY")0),--(O2:O30=DATE(2006,2,1)),--(O2:O30<=DATE(2006,2,28)),--(K2:K30="Type1"),L2:L30)

adapt to fit your data, having said that I can only assume that if you use
A2:A65000 this workbook will be very slow

--
Regards,

Peo Sjoblom

Portland, Oregon




"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can answer...

I'm trying to sum #s into a table from a separate document (the source doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around the
length problem?
5. Last one! I'd rather just have it search the whole column instead of
specific rows, but when I tried A:A it gave an error. Any way to do that?

On the formulas I do have with less OR possibilities, the formula doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' statement w

Hi!

Don't ya just love long sheet/file names and even longer paths with as many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then refer to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can answer...

I'm trying to sum #s into a table from a separate document (the source doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around the
length problem?
5. Last one! I'd rather just have it search the whole column instead of
specific rows, but when I tried A:A it gave an error. Any way to do that?

On the formulas I do have with less OR possibilities, the formula doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
creativeops
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

Great thanks everybody for the excellent solutions & suggestions. (Sorry
about breaking the 1Q rule - didn't know!).

Question for you Biff - the 'using cells to hold ALL variable criteria then
refer to those cells' idea seems great, but where would I do that? Just on
the same worksheet?

Thanks again everyone!

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with as many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then refer to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can answer...

I'm trying to sum #s into a table from a separate document (the source doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around the
length problem?
5. Last one! I'd rather just have it search the whole column instead of
specific rows, but when I tried A:A it gave an error. Any way to do that?

On the formulas I do have with less OR possibilities, the formula doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
creativeops
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March 3,
2006? Do you mean it would evaluate to March 2, 2006?
Thanks

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with as many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then refer to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can answer...

I'm trying to sum #s into a table from a separate document (the source doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around the
length problem?
5. Last one! I'd rather just have it search the whole column instead of
specific rows, but when I tried A:A it gave an error. Any way to do that?

On the formulas I do have with less OR possibilities, the formula doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

Hi!

==========
Question for you Biff - the 'using cells to hold ALL variable criteria then
refer to those cells' idea seems great, but where would I do that? Just on
the same worksheet?
==========
You can put them anywhere (on any sheet, in any cells) but it's better to
keep them on the same sheet and in close proximity to the formula(s) that
are referring to them (if possible!).

==========
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
3,
2006? Do you mean it would evaluate to March 2, 2006?
==========

Try this:

Enter this formula in a cell:

=DATE(2006,2,31)

What result do you get?

Excel "knows" that there is no Feb 31 2006. So it automatically offsets the
the difference to the next month. It will do the same thing for the month:

=DATE(2005,13,1)

There is no month 13 so it offsets the difference to the next year:

=DATE(2005,13,1) = Jan 1 2006

Biff

"creativeops" wrote in message
...
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
3,
2006? Do you mean it would evaluate to March 2, 2006?
Thanks

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with as
many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then refer to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can
answer...

I'm trying to sum #s into a table from a separate document (the source
doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I
make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a
limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around
the
length problem?
5. Last one! I'd rather just have it search the whole column instead
of
specific rows, but when I tried A:A it gave an error. Any way to do
that?

On the formulas I do have with less OR possibilities, the formula
doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
creativeops
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

Ok good, I put them close by on the same sheet and that works great.
And yeah, I realized the date thing right after I asked you - once again the
machine is smarter than I thought!

"Biff" wrote:

Hi!

==========
Question for you Biff - the 'using cells to hold ALL variable criteria then
refer to those cells' idea seems great, but where would I do that? Just on
the same worksheet?
==========
You can put them anywhere (on any sheet, in any cells) but it's better to
keep them on the same sheet and in close proximity to the formula(s) that
are referring to them (if possible!).

==========
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
3,
2006? Do you mean it would evaluate to March 2, 2006?
==========

Try this:

Enter this formula in a cell:

=DATE(2006,2,31)

What result do you get?

Excel "knows" that there is no Feb 31 2006. So it automatically offsets the
the difference to the next month. It will do the same thing for the month:

=DATE(2005,13,1)

There is no month 13 so it offsets the difference to the next year:

=DATE(2005,13,1) = Jan 1 2006

Biff

"creativeops" wrote in message
...
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
3,
2006? Do you mean it would evaluate to March 2, 2006?
Thanks

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with as
many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then refer to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can
answer...

I'm trying to sum #s into a table from a separate document (the source
doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I
make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a
limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around
the
length problem?
5. Last one! I'd rather just have it search the whole column instead
of
specific rows, but when I tried A:A it gave an error. Any way to do
that?

On the formulas I do have with less OR possibilities, the formula
doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

Just to add to the last reply..........

Using cells to hold the variable criteria enables you to simply enter new
criteria and not have to edit the formula.

Biff

"Biff" wrote in message
...
Hi!

==========
Question for you Biff - the 'using cells to hold ALL variable criteria
then
refer to those cells' idea seems great, but where would I do that? Just
on
the same worksheet?
==========
You can put them anywhere (on any sheet, in any cells) but it's better to
keep them on the same sheet and in close proximity to the formula(s) that
are referring to them (if possible!).

==========
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
3,
2006? Do you mean it would evaluate to March 2, 2006?
==========

Try this:

Enter this formula in a cell:

=DATE(2006,2,31)

What result do you get?

Excel "knows" that there is no Feb 31 2006. So it automatically offsets
the the difference to the next month. It will do the same thing for the
month:

=DATE(2005,13,1)

There is no month 13 so it offsets the difference to the next year:

=DATE(2005,13,1) = Jan 1 2006

Biff

"creativeops" wrote in message
...
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
March 3,
2006? Do you mean it would evaluate to March 2, 2006?
Thanks

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with as
many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then refer
to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can
answer...

I'm trying to sum #s into a table from a separate document (the source
doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I
make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a
limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around
the
length problem?
5. Last one! I'd rather just have it search the whole column instead
of
specific rows, but when I tried A:A it gave an error. Any way to do
that?

On the formulas I do have with less OR possibilities, the formula
doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
creativeops
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

wouldn't you still have to edit the formula to include the new criteria's cell?

"Biff" wrote:

Just to add to the last reply..........

Using cells to hold the variable criteria enables you to simply enter new
criteria and not have to edit the formula.

Biff

"Biff" wrote in message
...
Hi!

==========
Question for you Biff - the 'using cells to hold ALL variable criteria
then
refer to those cells' idea seems great, but where would I do that? Just
on
the same worksheet?
==========
You can put them anywhere (on any sheet, in any cells) but it's better to
keep them on the same sheet and in close proximity to the formula(s) that
are referring to them (if possible!).

==========
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
3,
2006? Do you mean it would evaluate to March 2, 2006?
==========

Try this:

Enter this formula in a cell:

=DATE(2006,2,31)

What result do you get?

Excel "knows" that there is no Feb 31 2006. So it automatically offsets
the the difference to the next month. It will do the same thing for the
month:

=DATE(2005,13,1)

There is no month 13 so it offsets the difference to the next year:

=DATE(2005,13,1) = Jan 1 2006

Biff

"creativeops" wrote in message
...
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
March 3,
2006? Do you mean it would evaluate to March 2, 2006?
Thanks

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with as
many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then refer
to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in message
...
Okay, multipart question that I'm really hoping you wizards can
answer...

I'm trying to sum #s into a table from a separate document (the source
doc
is on our company intranet, hence the '...' below - that is a lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London" AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can I
make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a
limit
to
# of characters
4. Assuming I could make an OR statement work how would I get around
the
length problem?
5. Last one! I'd rather just have it search the whole column instead
of
specific rows, but when I tried A:A it gave an error. Any way to do
that?

On the formulas I do have with less OR possibilities, the formula
doesn't
result in error, but it does result in 0 when it definitely shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))










  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

wouldn't you still have to edit the formula to include the new criteria's
cell?


If you were adding additional criteria, yes.

But, if the criteria you already have counts the number of gizmos that are
red in color and you want to count the number of gizmos that are black, all
you have to do is change the cell that holds the criteria red to black.

Biff

"creativeops" wrote in message
...
wouldn't you still have to edit the formula to include the new criteria's
cell?

"Biff" wrote:

Just to add to the last reply..........

Using cells to hold the variable criteria enables you to simply enter new
criteria and not have to edit the formula.

Biff

"Biff" wrote in message
...
Hi!

==========
Question for you Biff - the 'using cells to hold ALL variable criteria
then
refer to those cells' idea seems great, but where would I do that?
Just
on
the same worksheet?
==========
You can put them anywhere (on any sheet, in any cells) but it's better
to
keep them on the same sheet and in close proximity to the formula(s)
that
are referring to them (if possible!).

==========
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
March
3,
2006? Do you mean it would evaluate to March 2, 2006?
==========

Try this:

Enter this formula in a cell:

=DATE(2006,2,31)

What result do you get?

Excel "knows" that there is no Feb 31 2006. So it automatically offsets
the the difference to the next month. It will do the same thing for the
month:

=DATE(2005,13,1)

There is no month 13 so it offsets the difference to the next year:

=DATE(2005,13,1) = Jan 1 2006

Biff

"creativeops" wrote in message
...
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
March 3,
2006? Do you mean it would evaluate to March 2, 2006?
Thanks

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with
as
many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then
refer
to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in
message
...
Okay, multipart question that I'm really hoping you wizards can
answer...

I'm trying to sum #s into a table from a separate document (the
source
doc
is on our company intranet, hence the '...' below - that is a
lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London"
AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can
I
make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a
limit
to
# of characters
4. Assuming I could make an OR statement work how would I get
around
the
length problem?
5. Last one! I'd rather just have it search the whole column
instead
of
specific rows, but when I tried A:A it gave an error. Any way to
do
that?

On the formulas I do have with less OR possibilities, the formula
doesn't
result in error, but it does result in 0 when it definitely
shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))










  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
creativeops
 
Posts: n/a
Default sumproduct formula too long, & how to use make an 'OR' stateme

right - alright cool, thanks a lot

"Biff" wrote:

wouldn't you still have to edit the formula to include the new criteria's
cell?


If you were adding additional criteria, yes.

But, if the criteria you already have counts the number of gizmos that are
red in color and you want to count the number of gizmos that are black, all
you have to do is change the cell that holds the criteria red to black.

Biff

"creativeops" wrote in message
...
wouldn't you still have to edit the formula to include the new criteria's
cell?

"Biff" wrote:

Just to add to the last reply..........

Using cells to hold the variable criteria enables you to simply enter new
criteria and not have to edit the formula.

Biff

"Biff" wrote in message
...
Hi!

==========
Question for you Biff - the 'using cells to hold ALL variable criteria
then
refer to those cells' idea seems great, but where would I do that?
Just
on
the same worksheet?
==========
You can put them anywhere (on any sheet, in any cells) but it's better
to
keep them on the same sheet and in close proximity to the formula(s)
that
are referring to them (if possible!).

==========
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
March
3,
2006? Do you mean it would evaluate to March 2, 2006?
==========

Try this:

Enter this formula in a cell:

=DATE(2006,2,31)

What result do you get?

Excel "knows" that there is no Feb 31 2006. So it automatically offsets
the the difference to the next month. It will do the same thing for the
month:

=DATE(2005,13,1)

There is no month 13 so it offsets the difference to the next year:

=DATE(2005,13,1) = Jan 1 2006

Biff

"creativeops" wrote in message
...
Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
March 3,
2006? Do you mean it would evaluate to March 2, 2006?
Thanks

"Biff" wrote:

Hi!

Don't ya just love long sheet/file names and even longer paths with
as
many
subdirectories as is permitted?

Ok, now that we have that out of the way........

The best way to use an "or" type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{"London","Munich","Paris"}, 0))),............................

Even better, use cells to hold ALL of the variable criteria then
refer
to
those cells:

B1 = London
B2 = Munich
B3 = Paris
C1 = Brand X
C2 = Brand Y
C3 = Brand Z
D1 = 2/1/2006
D2 = 2/28/2006
E1 = Type 1

There is no 2/31/2006 as you have in your Date function. As written:

DATE(2006,2,31)

Evaluates to: Mar 3 2006

Now, here's your efficient formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(range=D1),--(range<=D2),--(range=E1),range)

Biff

"creativeops" wrote in
message
...
Okay, multipart question that I'm really hoping you wizards can
answer...

I'm trying to sum #s into a table from a separate document (the
source
doc
is on our company intranet, hence the '...' below - that is a
lonngg
intranet
address). The #s to be summed have to meet a variety of criteria.
However,
it often has to meet criteria A or B (or C) in the same column, and
similar
in other columns. AND to make it worse there's a date range.

1. Is the formula below is asking for column A to contain "London"
AND
"Munich" etc, instead of "London" OR "Munich" etc. If so, how can
I
make
it
an OR statement. (Same goes for the brand info in column J)
2. Does the date range setup look like it should work?
3. Is there a limit to the amount of criteria in a sumproduct or a
limit
to
# of characters
4. Assuming I could make an OR statement work how would I get
around
the
length problem?
5. Last one! I'd rather just have it search the whole column
instead
of
specific rows, but when I tried A:A it gave an error. Any way to
do
that?

On the formulas I do have with less OR possibilities, the formula
doesn't
result in error, but it does result in 0 when it definitely
shouldn't.

Sorry for the length!! Thanks so much for any help!!!
Ross

=SUMPRODUCT(--('...'!A2:A65000="London"),--('...'!A2:A65000="Munich"),--('...'!A2:A65000="Paris"),--('...'!J2:J65000="BrandX"),--('...'!J2:J65000="BrandY"),--('...!J2:J65000="BrandZ"),--('...'!O2:O65000<=DATE(2006,2,31)),--('...'!O2:O65000=DATE(2006,2,1)),--('...'!K2:K65000="Type
1"),--('...'!L2:L65000))











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
DIV/0! error in SumProduct formula with no division Irrational Excel Worksheet Functions 1 August 18th 05 01:39 AM
more than 3 conditions in conditional formatting - possible? rob curtis Excel Discussion (Misc queries) 11 August 17th 05 04:02 PM
Formula too long glenlisa Excel Discussion (Misc queries) 3 August 9th 05 07:34 AM
Problem with VBA returning the contents of a long formula. [email protected] Excel Discussion (Misc queries) 2 February 23rd 05 01:14 AM


All times are GMT +1. The time now is 05:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"