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