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