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