software

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 quot;Londonquot; AND
quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; 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=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type 1quot;),--('...'!L2:L65000))

Sticking to only 1 Q per post (that's actually supposed to be the quot;rulequot;)
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=quot;Londonquot;),--(B1:B10=quot;Munichquot;))

This AND construct however:
=SUMPRODUCT(--(A1:A10=quot;Londonquot;),--(A1:A10=quot;Munichquot;))
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={quot;Londonquot;,quot;Munichquot;}))

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
savefile.com/projects/236895
xdemechanik
---
quot;creativeopsquot; gt; wrote in message
...
gt; Okay, multipart question that I'm really hoping you wizards can answer...
gt;
gt; I'm trying to sum #s into a table from a separate document (the source doc
gt; is on our company intranet, hence the '...' below - that is a lonngg
intranet
gt; address). The #s to be summed have to meet a variety of criteria.
However,
gt; it often has to meet criteria A or B (or C) in the same column, and
similar
gt; in other columns. AND to make it worse there's a date range.
gt;
gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I make
it
gt; an OR statement. (Same goes for the brand info in column J)
gt; 2. Does the date range setup look like it should work?
gt; 3. Is there a limit to the amount of criteria in a sumproduct or a limit
to
gt; # of characters
gt; 4. Assuming I could make an OR statement work how would I get around the
gt; length problem?
gt; 5. Last one! I'd rather just have it search the whole column instead of
gt; specific rows, but when I tried A:A it gave an error. Any way to do that?
gt;
gt; On the formulas I do have with less OR possibilities, the formula doesn't
gt; result in error, but it does result in 0 when it definitely shouldn't.
gt;
gt; Sorry for the length!! Thanks so much for any help!!!
gt; Ross
gt;
gt;
=SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('..
..'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;Brand
Yquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...
'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
1quot;),--('...'!L2:L65000))
Assuming you want to use it as OR instead of AND?

=SUMPRODUCT(--((A2:A30=quot;Londonquot;) (A2:A30=quot;Munichquot;)gt;0),--((J2:J30=quot;BrandXquot;) (J2:J30=quot;BrandYquot;)gt;0),--(O2:O30gt;=DATE(2006,2,1)),--(O2:O30lt;=DATE(2006,2,28)),--(K2:K30=quot;Type1quot;),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

quot;creativeopsquot; gt; wrote in message
...
gt; Okay, multipart question that I'm really hoping you wizards can answer...
gt;
gt; I'm trying to sum #s into a table from a separate document (the source doc
gt; is on our company intranet, hence the '...' below - that is a lonngg
gt; intranet
gt; address). The #s to be summed have to meet a variety of criteria.
gt; However,
gt; it often has to meet criteria A or B (or C) in the same column, and
gt; similar
gt; in other columns. AND to make it worse there's a date range.
gt;
gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I make
gt; it
gt; an OR statement. (Same goes for the brand info in column J)
gt; 2. Does the date range setup look like it should work?
gt; 3. Is there a limit to the amount of criteria in a sumproduct or a limit
gt; to
gt; # of characters
gt; 4. Assuming I could make an OR statement work how would I get around the
gt; length problem?
gt; 5. Last one! I'd rather just have it search the whole column instead of
gt; specific rows, but when I tried A:A it gave an error. Any way to do that?
gt;
gt; On the formulas I do have with less OR possibilities, the formula doesn't
gt; result in error, but it does result in 0 when it definitely shouldn't.
gt;
gt; Sorry for the length!! Thanks so much for any help!!!
gt; Ross
gt;
gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt; 1quot;),--('...'!L2:L65000))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 quot;orquot; type of expression in Sumproduct is:

=SUMPRODUCT(--(ISNUMBER(MATCH(range,{quot;Londonquot;,quot;Munichquot;,quot;Parisquot;}, 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))),--(rangegt;=D1),--(rangelt;=D2),--(range=E1),range)

Biff

quot;creativeopsquot; gt; wrote in message
...
gt; Okay, multipart question that I'm really hoping you wizards can answer...
gt;
gt; I'm trying to sum #s into a table from a separate document (the source doc
gt; is on our company intranet, hence the '...' below - that is a lonngg
gt; intranet
gt; address). The #s to be summed have to meet a variety of criteria.
gt; However,
gt; it often has to meet criteria A or B (or C) in the same column, and
gt; similar
gt; in other columns. AND to make it worse there's a date range.
gt;
gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I make
gt; it
gt; an OR statement. (Same goes for the brand info in column J)
gt; 2. Does the date range setup look like it should work?
gt; 3. Is there a limit to the amount of criteria in a sumproduct or a limit
gt; to
gt; # of characters
gt; 4. Assuming I could make an OR statement work how would I get around the
gt; length problem?
gt; 5. Last one! I'd rather just have it search the whole column instead of
gt; specific rows, but when I tried A:A it gave an error. Any way to do that?
gt;
gt; On the formulas I do have with less OR possibilities, the formula doesn't
gt; result in error, but it does result in 0 when it definitely shouldn't.
gt;
gt; Sorry for the length!! Thanks so much for any help!!!
gt; Ross
gt;
gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt; 1quot;),--('...'!L2:L65000))
Great thanks everybody for the excellent solutions amp; 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!

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Don't ya just love long sheet/file names and even longer paths with as many
gt; subdirectories as is permitted?
gt;
gt; Ok, now that we have that out of the way........
gt;
gt; The best way to use an quot;orquot; type of expression in Sumproduct is:
gt;
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,{quot;Londonquot;,quot;Munichquot;,quot;Parisquot;}, 0))),............................
gt;
gt; Even better, use cells to hold ALL of the variable criteria then refer to
gt; those cells:
gt;
gt; B1 = London
gt; B2 = Munich
gt; B3 = Paris
gt; C1 = Brand X
gt; C2 = Brand Y
gt; C3 = Brand Z
gt; D1 = 2/1/2006
gt; D2 = 2/28/2006
gt; E1 = Type 1
gt;
gt; There is no 2/31/2006 as you have in your Date function. As written:
gt;
gt; DATE(2006,2,31)
gt;
gt; Evaluates to: Mar 3 2006
gt;
gt; Now, here's your efficient formula:
gt;
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(rangegt;=D1),--(rangelt;=D2),--(range=E1),range)
gt;
gt; Biff
gt;
gt; quot;creativeopsquot; gt; wrote in message
gt; ...
gt; gt; Okay, multipart question that I'm really hoping you wizards can answer...
gt; gt;
gt; gt; I'm trying to sum #s into a table from a separate document (the source doc
gt; gt; is on our company intranet, hence the '...' below - that is a lonngg
gt; gt; intranet
gt; gt; address). The #s to be summed have to meet a variety of criteria.
gt; gt; However,
gt; gt; it often has to meet criteria A or B (or C) in the same column, and
gt; gt; similar
gt; gt; in other columns. AND to make it worse there's a date range.
gt; gt;
gt; gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt; gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I make
gt; gt; it
gt; gt; an OR statement. (Same goes for the brand info in column J)
gt; gt; 2. Does the date range setup look like it should work?
gt; gt; 3. Is there a limit to the amount of criteria in a sumproduct or a limit
gt; gt; to
gt; gt; # of characters
gt; gt; 4. Assuming I could make an OR statement work how would I get around the
gt; gt; length problem?
gt; gt; 5. Last one! I'd rather just have it search the whole column instead of
gt; gt; specific rows, but when I tried A:A it gave an error. Any way to do that?
gt; gt;
gt; gt; On the formulas I do have with less OR possibilities, the formula doesn't
gt; gt; result in error, but it does result in 0 when it definitely shouldn't.
gt; gt;
gt; gt; Sorry for the length!! Thanks so much for any help!!!
gt; gt; Ross
gt; gt;
gt; gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt; gt; 1quot;),--('...'!L2:L65000))
gt;
gt;
gt;

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

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Don't ya just love long sheet/file names and even longer paths with as many
gt; subdirectories as is permitted?
gt;
gt; Ok, now that we have that out of the way........
gt;
gt; The best way to use an quot;orquot; type of expression in Sumproduct is:
gt;
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,{quot;Londonquot;,quot;Munichquot;,quot;Parisquot;}, 0))),............................
gt;
gt; Even better, use cells to hold ALL of the variable criteria then refer to
gt; those cells:
gt;
gt; B1 = London
gt; B2 = Munich
gt; B3 = Paris
gt; C1 = Brand X
gt; C2 = Brand Y
gt; C3 = Brand Z
gt; D1 = 2/1/2006
gt; D2 = 2/28/2006
gt; E1 = Type 1
gt;
gt; There is no 2/31/2006 as you have in your Date function. As written:
gt;
gt; DATE(2006,2,31)
gt;
gt; Evaluates to: Mar 3 2006
gt;
gt; Now, here's your efficient formula:
gt;
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(rangegt;=D1),--(rangelt;=D2),--(range=E1),range)
gt;
gt; Biff
gt;
gt; quot;creativeopsquot; gt; wrote in message
gt; ...
gt; gt; Okay, multipart question that I'm really hoping you wizards can answer...
gt; gt;
gt; gt; I'm trying to sum #s into a table from a separate document (the source doc
gt; gt; is on our company intranet, hence the '...' below - that is a lonngg
gt; gt; intranet
gt; gt; address). The #s to be summed have to meet a variety of criteria.
gt; gt; However,
gt; gt; it often has to meet criteria A or B (or C) in the same column, and
gt; gt; similar
gt; gt; in other columns. AND to make it worse there's a date range.
gt; gt;
gt; gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt; gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I make
gt; gt; it
gt; gt; an OR statement. (Same goes for the brand info in column J)
gt; gt; 2. Does the date range setup look like it should work?
gt; gt; 3. Is there a limit to the amount of criteria in a sumproduct or a limit
gt; gt; to
gt; gt; # of characters
gt; gt; 4. Assuming I could make an OR statement work how would I get around the
gt; gt; length problem?
gt; gt; 5. Last one! I'd rather just have it search the whole column instead of
gt; gt; specific rows, but when I tried A:A it gave an error. Any way to do that?
gt; gt;
gt; gt; On the formulas I do have with less OR possibilities, the formula doesn't
gt; gt; result in error, but it does result in 0 when it definitely shouldn't.
gt; gt;
gt; gt; Sorry for the length!! Thanks so much for any help!!!
gt; gt; Ross
gt; gt;
gt; gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt; gt; 1quot;),--('...'!L2:L65000))
gt;
gt;
gt;

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 quot;knowsquot; 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

quot;creativeopsquot; gt; wrote in message
...
gt; Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
gt; 3,
gt; 2006? Do you mean it would evaluate to March 2, 2006?
gt; Thanks
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Don't ya just love long sheet/file names and even longer paths with as
gt;gt; many
gt;gt; subdirectories as is permitted?
gt;gt;
gt;gt; Ok, now that we have that out of the way........
gt;gt;
gt;gt; The best way to use an quot;orquot; type of expression in Sumproduct is:
gt;gt;
gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,{quot;Londonquot;,quot;Munichquot;,quot;Parisquot;}, 0))),............................
gt;gt;
gt;gt; Even better, use cells to hold ALL of the variable criteria then refer to
gt;gt; those cells:
gt;gt;
gt;gt; B1 = London
gt;gt; B2 = Munich
gt;gt; B3 = Paris
gt;gt; C1 = Brand X
gt;gt; C2 = Brand Y
gt;gt; C3 = Brand Z
gt;gt; D1 = 2/1/2006
gt;gt; D2 = 2/28/2006
gt;gt; E1 = Type 1
gt;gt;
gt;gt; There is no 2/31/2006 as you have in your Date function. As written:
gt;gt;
gt;gt; DATE(2006,2,31)
gt;gt;
gt;gt; Evaluates to: Mar 3 2006
gt;gt;
gt;gt; Now, here's your efficient formula:
gt;gt;
gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(rangegt;=D1),--(rangelt;=D2),--(range=E1),range)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;creativeopsquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Okay, multipart question that I'm really hoping you wizards can
gt;gt; gt; answer...
gt;gt; gt;
gt;gt; gt; I'm trying to sum #s into a table from a separate document (the source
gt;gt; gt; doc
gt;gt; gt; is on our company intranet, hence the '...' below - that is a lonngg
gt;gt; gt; intranet
gt;gt; gt; address). The #s to be summed have to meet a variety of criteria.
gt;gt; gt; However,
gt;gt; gt; it often has to meet criteria A or B (or C) in the same column, and
gt;gt; gt; similar
gt;gt; gt; in other columns. AND to make it worse there's a date range.
gt;gt; gt;
gt;gt; gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt;gt; gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I
gt;gt; gt; make
gt;gt; gt; it
gt;gt; gt; an OR statement. (Same goes for the brand info in column J)
gt;gt; gt; 2. Does the date range setup look like it should work?
gt;gt; gt; 3. Is there a limit to the amount of criteria in a sumproduct or a
gt;gt; gt; limit
gt;gt; gt; to
gt;gt; gt; # of characters
gt;gt; gt; 4. Assuming I could make an OR statement work how would I get around
gt;gt; gt; the
gt;gt; gt; length problem?
gt;gt; gt; 5. Last one! I'd rather just have it search the whole column instead
gt;gt; gt; of
gt;gt; gt; specific rows, but when I tried A:A it gave an error. Any way to do
gt;gt; gt; that?
gt;gt; gt;
gt;gt; gt; On the formulas I do have with less OR possibilities, the formula
gt;gt; gt; doesn't
gt;gt; gt; result in error, but it does result in 0 when it definitely shouldn't.
gt;gt; gt;
gt;gt; gt; Sorry for the length!! Thanks so much for any help!!!
gt;gt; gt; Ross
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt;gt; gt; 1quot;),--('...'!L2:L65000))
gt;gt;
gt;gt;
gt;gt;
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!

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; ==========
gt; Question for you Biff - the 'using cells to hold ALL variable criteria then
gt; refer to those cells' idea seems great, but where would I do that? Just on
gt; the same worksheet?
gt; ==========
gt; You can put them anywhere (on any sheet, in any cells) but it's better to
gt; keep them on the same sheet and in close proximity to the formula(s) that
gt; are referring to them (if possible!).
gt;
gt; ==========
gt; Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
gt; 3,
gt; 2006? Do you mean it would evaluate to March 2, 2006?
gt; ==========
gt;
gt; Try this:
gt;
gt; Enter this formula in a cell:
gt;
gt; =DATE(2006,2,31)
gt;
gt; What result do you get?
gt;
gt; Excel quot;knowsquot; that there is no Feb 31 2006. So it automatically offsets the
gt; the difference to the next month. It will do the same thing for the month:
gt;
gt; =DATE(2005,13,1)
gt;
gt; There is no month 13 so it offsets the difference to the next year:
gt;
gt; =DATE(2005,13,1) = Jan 1 2006
gt;
gt; Biff
gt;
gt; quot;creativeopsquot; gt; wrote in message
gt; ...
gt; gt; Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
gt; gt; 3,
gt; gt; 2006? Do you mean it would evaluate to March 2, 2006?
gt; gt; Thanks
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; Don't ya just love long sheet/file names and even longer paths with as
gt; gt;gt; many
gt; gt;gt; subdirectories as is permitted?
gt; gt;gt;
gt; gt;gt; Ok, now that we have that out of the way........
gt; gt;gt;
gt; gt;gt; The best way to use an quot;orquot; type of expression in Sumproduct is:
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,{quot;Londonquot;,quot;Munichquot;,quot;Parisquot;}, 0))),............................
gt; gt;gt;
gt; gt;gt; Even better, use cells to hold ALL of the variable criteria then refer to
gt; gt;gt; those cells:
gt; gt;gt;
gt; gt;gt; B1 = London
gt; gt;gt; B2 = Munich
gt; gt;gt; B3 = Paris
gt; gt;gt; C1 = Brand X
gt; gt;gt; C2 = Brand Y
gt; gt;gt; C3 = Brand Z
gt; gt;gt; D1 = 2/1/2006
gt; gt;gt; D2 = 2/28/2006
gt; gt;gt; E1 = Type 1
gt; gt;gt;
gt; gt;gt; There is no 2/31/2006 as you have in your Date function. As written:
gt; gt;gt;
gt; gt;gt; DATE(2006,2,31)
gt; gt;gt;
gt; gt;gt; Evaluates to: Mar 3 2006
gt; gt;gt;
gt; gt;gt; Now, here's your efficient formula:
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(rangegt;=D1),--(rangelt;=D2),--(range=E1),range)
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;creativeopsquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Okay, multipart question that I'm really hoping you wizards can
gt; gt;gt; gt; answer...
gt; gt;gt; gt;
gt; gt;gt; gt; I'm trying to sum #s into a table from a separate document (the source
gt; gt;gt; gt; doc
gt; gt;gt; gt; is on our company intranet, hence the '...' below - that is a lonngg
gt; gt;gt; gt; intranet
gt; gt;gt; gt; address). The #s to be summed have to meet a variety of criteria.
gt; gt;gt; gt; However,
gt; gt;gt; gt; it often has to meet criteria A or B (or C) in the same column, and
gt; gt;gt; gt; similar
gt; gt;gt; gt; in other columns. AND to make it worse there's a date range.
gt; gt;gt; gt;
gt; gt;gt; gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt; gt;gt; gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I
gt; gt;gt; gt; make
gt; gt;gt; gt; it
gt; gt;gt; gt; an OR statement. (Same goes for the brand info in column J)
gt; gt;gt; gt; 2. Does the date range setup look like it should work?
gt; gt;gt; gt; 3. Is there a limit to the amount of criteria in a sumproduct or a
gt; gt;gt; gt; limit
gt; gt;gt; gt; to
gt; gt;gt; gt; # of characters
gt; gt;gt; gt; 4. Assuming I could make an OR statement work how would I get around
gt; gt;gt; gt; the
gt; gt;gt; gt; length problem?
gt; gt;gt; gt; 5. Last one! I'd rather just have it search the whole column instead
gt; gt;gt; gt; of
gt; gt;gt; gt; specific rows, but when I tried A:A it gave an error. Any way to do
gt; gt;gt; gt; that?
gt; gt;gt; gt;
gt; gt;gt; gt; On the formulas I do have with less OR possibilities, the formula
gt; gt;gt; gt; doesn't
gt; gt;gt; gt; result in error, but it does result in 0 when it definitely shouldn't.
gt; gt;gt; gt;
gt; gt;gt; gt; Sorry for the length!! Thanks so much for any help!!!
gt; gt;gt; gt; Ross
gt; gt;gt; gt;
gt; gt;gt; gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt; gt;gt; gt; 1quot;),--('...'!L2:L65000))
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; ==========
gt; Question for you Biff - the 'using cells to hold ALL variable criteria
gt; then
gt; refer to those cells' idea seems great, but where would I do that? Just
gt; on
gt; the same worksheet?
gt; ==========
gt; You can put them anywhere (on any sheet, in any cells) but it's better to
gt; keep them on the same sheet and in close proximity to the formula(s) that
gt; are referring to them (if possible!).
gt;
gt; ==========
gt; Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
gt; 3,
gt; 2006? Do you mean it would evaluate to March 2, 2006?
gt; ==========
gt;
gt; Try this:
gt;
gt; Enter this formula in a cell:
gt;
gt; =DATE(2006,2,31)
gt;
gt; What result do you get?
gt;
gt; Excel quot;knowsquot; that there is no Feb 31 2006. So it automatically offsets
gt; the the difference to the next month. It will do the same thing for the
gt; month:
gt;
gt; =DATE(2005,13,1)
gt;
gt; There is no month 13 so it offsets the difference to the next year:
gt;
gt; =DATE(2005,13,1) = Jan 1 2006
gt;
gt; Biff
gt;
gt; quot;creativeopsquot; gt; wrote in message
gt; ...
gt;gt; Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
gt;gt; March 3,
gt;gt; 2006? Do you mean it would evaluate to March 2, 2006?
gt;gt; Thanks
gt;gt;
gt;gt; quot;Biffquot; wrote:
gt;gt;
gt;gt;gt; Hi!
gt;gt;gt;
gt;gt;gt; Don't ya just love long sheet/file names and even longer paths with as
gt;gt;gt; many
gt;gt;gt; subdirectories as is permitted?
gt;gt;gt;
gt;gt;gt; Ok, now that we have that out of the way........
gt;gt;gt;
gt;gt;gt; The best way to use an quot;orquot; type of expression in Sumproduct is:
gt;gt;gt;
gt;gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,{quot;Londonquot;,quot;Munichquot;,quot;Parisquot;}, 0))),............................
gt;gt;gt;
gt;gt;gt; Even better, use cells to hold ALL of the variable criteria then refer
gt;gt;gt; to
gt;gt;gt; those cells:
gt;gt;gt;
gt;gt;gt; B1 = London
gt;gt;gt; B2 = Munich
gt;gt;gt; B3 = Paris
gt;gt;gt; C1 = Brand X
gt;gt;gt; C2 = Brand Y
gt;gt;gt; C3 = Brand Z
gt;gt;gt; D1 = 2/1/2006
gt;gt;gt; D2 = 2/28/2006
gt;gt;gt; E1 = Type 1
gt;gt;gt;
gt;gt;gt; There is no 2/31/2006 as you have in your Date function. As written:
gt;gt;gt;
gt;gt;gt; DATE(2006,2,31)
gt;gt;gt;
gt;gt;gt; Evaluates to: Mar 3 2006
gt;gt;gt;
gt;gt;gt; Now, here's your efficient formula:
gt;gt;gt;
gt;gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(rangegt;=D1),--(rangelt;=D2),--(range=E1),range)
gt;gt;gt;
gt;gt;gt; Biff
gt;gt;gt;
gt;gt;gt; quot;creativeopsquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt; gt; Okay, multipart question that I'm really hoping you wizards can
gt;gt;gt; gt; answer...
gt;gt;gt; gt;
gt;gt;gt; gt; I'm trying to sum #s into a table from a separate document (the source
gt;gt;gt; gt; doc
gt;gt;gt; gt; is on our company intranet, hence the '...' below - that is a lonngg
gt;gt;gt; gt; intranet
gt;gt;gt; gt; address). The #s to be summed have to meet a variety of criteria.
gt;gt;gt; gt; However,
gt;gt;gt; gt; it often has to meet criteria A or B (or C) in the same column, and
gt;gt;gt; gt; similar
gt;gt;gt; gt; in other columns. AND to make it worse there's a date range.
gt;gt;gt; gt;
gt;gt;gt; gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt;gt;gt; gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I
gt;gt;gt; gt; make
gt;gt;gt; gt; it
gt;gt;gt; gt; an OR statement. (Same goes for the brand info in column J)
gt;gt;gt; gt; 2. Does the date range setup look like it should work?
gt;gt;gt; gt; 3. Is there a limit to the amount of criteria in a sumproduct or a
gt;gt;gt; gt; limit
gt;gt;gt; gt; to
gt;gt;gt; gt; # of characters
gt;gt;gt; gt; 4. Assuming I could make an OR statement work how would I get around
gt;gt;gt; gt; the
gt;gt;gt; gt; length problem?
gt;gt;gt; gt; 5. Last one! I'd rather just have it search the whole column instead
gt;gt;gt; gt; of
gt;gt;gt; gt; specific rows, but when I tried A:A it gave an error. Any way to do
gt;gt;gt; gt; that?
gt;gt;gt; gt;
gt;gt;gt; gt; On the formulas I do have with less OR possibilities, the formula
gt;gt;gt; gt; doesn't
gt;gt;gt; gt; result in error, but it does result in 0 when it definitely shouldn't.
gt;gt;gt; gt;
gt;gt;gt; gt; Sorry for the length!! Thanks so much for any help!!!
gt;gt;gt; gt; Ross
gt;gt;gt; gt;
gt;gt;gt; gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt;gt;gt; gt; 1quot;),--('...'!L2:L65000))
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;
wouldn't you still have to edit the formula to include the new criteria's cell?

quot;Biffquot; wrote:

gt; Just to add to the last reply..........
gt;
gt; Using cells to hold the variable criteria enables you to simply enter new
gt; criteria and not have to edit the formula.
gt;
gt; Biff
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; gt; Hi!
gt; gt;
gt; gt; ==========
gt; gt; Question for you Biff - the 'using cells to hold ALL variable criteria
gt; gt; then
gt; gt; refer to those cells' idea seems great, but where would I do that? Just
gt; gt; on
gt; gt; the same worksheet?
gt; gt; ==========
gt; gt; You can put them anywhere (on any sheet, in any cells) but it's better to
gt; gt; keep them on the same sheet and in close proximity to the formula(s) that
gt; gt; are referring to them (if possible!).
gt; gt;
gt; gt; ==========
gt; gt; Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to March
gt; gt; 3,
gt; gt; 2006? Do you mean it would evaluate to March 2, 2006?
gt; gt; ==========
gt; gt;
gt; gt; Try this:
gt; gt;
gt; gt; Enter this formula in a cell:
gt; gt;
gt; gt; =DATE(2006,2,31)
gt; gt;
gt; gt; What result do you get?
gt; gt;
gt; gt; Excel quot;knowsquot; that there is no Feb 31 2006. So it automatically offsets
gt; gt; the the difference to the next month. It will do the same thing for the
gt; gt; month:
gt; gt;
gt; gt; =DATE(2005,13,1)
gt; gt;
gt; gt; There is no month 13 so it offsets the difference to the next year:
gt; gt;
gt; gt; =DATE(2005,13,1) = Jan 1 2006
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;creativeopsquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Oh Biff, I also meant to ask you - why would '2006,2,31' evaluate to
gt; gt;gt; March 3,
gt; gt;gt; 2006? Do you mean it would evaluate to March 2, 2006?
gt; gt;gt; Thanks
gt; gt;gt;
gt; gt;gt; quot;Biffquot; wrote:
gt; gt;gt;
gt; gt;gt;gt; Hi!
gt; gt;gt;gt;
gt; gt;gt;gt; Don't ya just love long sheet/file names and even longer paths with as
gt; gt;gt;gt; many
gt; gt;gt;gt; subdirectories as is permitted?
gt; gt;gt;gt;
gt; gt;gt;gt; Ok, now that we have that out of the way........
gt; gt;gt;gt;
gt; gt;gt;gt; The best way to use an quot;orquot; type of expression in Sumproduct is:
gt; gt;gt;gt;
gt; gt;gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,{quot;Londonquot;,quot;Munichquot;,quot;Parisquot;}, 0))),............................
gt; gt;gt;gt;
gt; gt;gt;gt; Even better, use cells to hold ALL of the variable criteria then refer
gt; gt;gt;gt; to
gt; gt;gt;gt; those cells:
gt; gt;gt;gt;
gt; gt;gt;gt; B1 = London
gt; gt;gt;gt; B2 = Munich
gt; gt;gt;gt; B3 = Paris
gt; gt;gt;gt; C1 = Brand X
gt; gt;gt;gt; C2 = Brand Y
gt; gt;gt;gt; C3 = Brand Z
gt; gt;gt;gt; D1 = 2/1/2006
gt; gt;gt;gt; D2 = 2/28/2006
gt; gt;gt;gt; E1 = Type 1
gt; gt;gt;gt;
gt; gt;gt;gt; There is no 2/31/2006 as you have in your Date function. As written:
gt; gt;gt;gt;
gt; gt;gt;gt; DATE(2006,2,31)
gt; gt;gt;gt;
gt; gt;gt;gt; Evaluates to: Mar 3 2006
gt; gt;gt;gt;
gt; gt;gt;gt; Now, here's your efficient formula:
gt; gt;gt;gt;
gt; gt;gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(range,B1:B3,0))),--(ISNUMBER(MATCH(range,C1:C3,0))),--(rangegt;=D1),--(rangelt;=D2),--(range=E1),range)
gt; gt;gt;gt;
gt; gt;gt;gt; Biff
gt; gt;gt;gt;
gt; gt;gt;gt; quot;creativeopsquot; gt; wrote in message
gt; gt;gt;gt; ...
gt; gt;gt;gt; gt; Okay, multipart question that I'm really hoping you wizards can
gt; gt;gt;gt; gt; answer...
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; I'm trying to sum #s into a table from a separate document (the source
gt; gt;gt;gt; gt; doc
gt; gt;gt;gt; gt; is on our company intranet, hence the '...' below - that is a lonngg
gt; gt;gt;gt; gt; intranet
gt; gt;gt;gt; gt; address). The #s to be summed have to meet a variety of criteria.
gt; gt;gt;gt; gt; However,
gt; gt;gt;gt; gt; it often has to meet criteria A or B (or C) in the same column, and
gt; gt;gt;gt; gt; similar
gt; gt;gt;gt; gt; in other columns. AND to make it worse there's a date range.
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; 1. Is the formula below is asking for column A to contain quot;Londonquot; AND
gt; gt;gt;gt; gt; quot;Munichquot; etc, instead of quot;Londonquot; OR quot;Munichquot; etc. If so, how can I
gt; gt;gt;gt; gt; make
gt; gt;gt;gt; gt; it
gt; gt;gt;gt; gt; an OR statement. (Same goes for the brand info in column J)
gt; gt;gt;gt; gt; 2. Does the date range setup look like it should work?
gt; gt;gt;gt; gt; 3. Is there a limit to the amount of criteria in a sumproduct or a
gt; gt;gt;gt; gt; limit
gt; gt;gt;gt; gt; to
gt; gt;gt;gt; gt; # of characters
gt; gt;gt;gt; gt; 4. Assuming I could make an OR statement work how would I get around
gt; gt;gt;gt; gt; the
gt; gt;gt;gt; gt; length problem?
gt; gt;gt;gt; gt; 5. Last one! I'd rather just have it search the whole column instead
gt; gt;gt;gt; gt; of
gt; gt;gt;gt; gt; specific rows, but when I tried A:A it gave an error. Any way to do
gt; gt;gt;gt; gt; that?
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; On the formulas I do have with less OR possibilities, the formula
gt; gt;gt;gt; gt; doesn't
gt; gt;gt;gt; gt; result in error, but it does result in 0 when it definitely shouldn't.
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; Sorry for the length!! Thanks so much for any help!!!
gt; gt;gt;gt; gt; Ross
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; =SUMPRODUCT(--('...'!A2:A65000=quot;Londonquot;),--('...'!A2:A65000=quot;Munichquot;),--('...'!A2:A65000=quot;Parisquot;),--('...'!J2:J65000=quot;BrandXquot;),--('...'!J2:J65000=quot;BrandYquot;),--('...!J2:J65000=quot;BrandZquot;),--('...'!O2:O65000lt;=DATE(2006,2,31)),--('...'!O2:O65000gt;=DATE(2006,2,1)),--('...'!K2:K65000=quot;Type
gt; gt;gt;gt; gt; 1quot;),--('...'!L2:L65000))
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;
gt; gt;
gt;
gt;
gt;