sumproduct formula too long, & how to use make an 'OR' statement w

[Guest]

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

 

[Max]

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

---

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

 

[Peo Sjoblom]

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

 

[Biff]

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

 

[Guest]

Great thanks everybody for the excellent solutions & 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!

 

[Guest]

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]

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

 

[Guest]

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!

 

[Biff]

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

 

[Guest]

wouldn't you still have to edit the formula to include the new criteria's cell?

 

[Biff]

wouldn't you still have to edit the formula to include the new criteria's

cell?

If you were adding additional criteria, yes.

But, if the criteria you already have counts the number of gizmos that are
red in color and you want to count the number of gizmos that are black, all
you have to do is change the cell that holds the criteria red to black.

Biff

 

[Guest]

right - alright cool, thanks a lot

If you were adding additional criteria, yes.

But, if the criteria you already have counts the number of gizmos that are
red in color and you want to count the number of gizmos that are black, all
you have to do is change the cell that holds the criteria red to black.

Biff