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