Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' statement w
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)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' statement w
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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' statement w
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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' statement w
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)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
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! "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)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
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)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
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)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
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" wrote: 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)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
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 "Biff" wrote in message ... 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)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
wouldn't you still have to edit the formula to include the new criteria's cell?
"Biff" wrote: 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 "Biff" wrote in message ... 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)) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
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 "creativeops" wrote in message ... wouldn't you still have to edit the formula to include the new criteria's cell? "Biff" wrote: 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 "Biff" wrote in message ... 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)) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula too long, & how to use make an 'OR' stateme
right - alright cool, thanks a lot
"Biff" wrote: 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 "creativeops" wrote in message ... wouldn't you still have to edit the formula to include the new criteria's cell? "Biff" wrote: 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 "Biff" wrote in message ... 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
DIV/0! error in SumProduct formula with no division | Excel Worksheet Functions | |||
more than 3 conditions in conditional formatting - possible? | Excel Discussion (Misc queries) | |||
Formula too long | Excel Discussion (Misc queries) | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) |