|
Sumproduct & Named range
I am trying to use names in a sumproduct()
The origonal, which works =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(Data!$E$2:Data!$E$3000=$V145),Data!$H$2:Data!$H$3 000+Data!$K$2:Data!$K$3000+Data!$J$2:Data!$J$3000+ Data!$I$2:Data!$I$3000) NEW, does NOT work =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(DPilot=$V145),DDay+DNight+DNVG+DIFR) DDay = OFFSET(Data!$C$2,0,0,COUNTA(Data!$A:$A),1) [used $a:$a to extend to bottom of data filled] - Dnight,DNVG,Difr similar but use different column on same table I also tried DDay = offset(database,0,21) as the "refers to" named range Any thoughts, I was trying to make it easier to read Thanks SPB Office 2007 |
Sumproduct & Named range
Unless you're using XL07, Sumproduct and array formulas *cannot* reference
ENTIRE columns (A:A). Also, there's really no reason to include the sheet name twice when referencing a range. Data!$C$2:$C$3000 is sufficient. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Excel 2003 - SPB" wrote in message ... I am trying to use names in a sumproduct() The origonal, which works =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(Data!$E$2:Data!$E$3000=$V145),Data!$H$2:Data!$H$3 000+Data!$K$2:Data!$K$3000+Data!$J$2:Data!$J$3000+ Data!$I$2:Data!$I$3000) NEW, does NOT work =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(DPilot=$V145),DDay+DNight+DNVG+DIFR) DDay = OFFSET(Data!$C$2,0,0,COUNTA(Data!$A:$A),1) [used $a:$a to extend to bottom of data filled] - Dnight,DNVG,Difr similar but use different column on same table I also tried DDay = offset(database,0,21) as the "refers to" named range Any thoughts, I was trying to make it easier to read Thanks SPB Office 2007 |
Sumproduct & Named range
I am running 2007
Thanks for the sheet naming twice tip any thoughts on the "Named" ranges SPB "RagDyeR" wrote: Unless you're using XL07, Sumproduct and array formulas *cannot* reference ENTIRE columns (A:A). Also, there's really no reason to include the sheet name twice when referencing a range. Data!$C$2:$C$3000 is sufficient. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Excel 2003 - SPB" wrote in message ... I am trying to use names in a sumproduct() The origonal, which works =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(Data!$E$2:Data!$E$3000=$V145),Data!$H$2:Data!$H$3 000+Data!$K$2:Data!$K$3000+Data!$J$2:Data!$J$3000+ Data!$I$2:Data!$I$3000) NEW, does NOT work =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(DPilot=$V145),DDay+DNight+DNVG+DIFR) DDay = OFFSET(Data!$C$2,0,0,COUNTA(Data!$A:$A),1) [used $a:$a to extend to bottom of data filled] - Dnight,DNVG,Difr similar but use different column on same table I also tried DDay = offset(database,0,21) as the "refers to" named range Any thoughts, I was trying to make it easier to read Thanks SPB Office 2007 |
Sumproduct & Named range
What does "does not work" means?
-- Regards, Peo Sjoblom "Excel 2003 - SPB" wrote in message ... I am running 2007 Thanks for the sheet naming twice tip any thoughts on the "Named" ranges SPB "RagDyeR" wrote: Unless you're using XL07, Sumproduct and array formulas *cannot* reference ENTIRE columns (A:A). Also, there's really no reason to include the sheet name twice when referencing a range. Data!$C$2:$C$3000 is sufficient. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Excel 2003 - SPB" wrote in message ... I am trying to use names in a sumproduct() The origonal, which works =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(Data!$E$2:Data!$E$3000=$V145),Data!$H$2:Data!$H$3 000+Data!$K$2:Data!$K$3000+Data!$J$2:Data!$J$3000+ Data!$I$2:Data!$I$3000) NEW, does NOT work =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(DPilot=$V145),DDay+DNight+DNVG+DIFR) DDay = OFFSET(Data!$C$2,0,0,COUNTA(Data!$A:$A),1) [used $a:$a to extend to bottom of data filled] - Dnight,DNVG,Difr similar but use different column on same table I also tried DDay = offset(database,0,21) as the "refers to" named range Any thoughts, I was trying to make it easier to read Thanks SPB Office 2007 |
Sumproduct & Named range
I get a #N/A result
"Peo Sjoblom" wrote: What does "does not work" means? -- Regards, Peo Sjoblom "Excel 2003 - SPB" wrote in message ... I am running 2007 Thanks for the sheet naming twice tip any thoughts on the "Named" ranges SPB "RagDyeR" wrote: Unless you're using XL07, Sumproduct and array formulas *cannot* reference ENTIRE columns (A:A). Also, there's really no reason to include the sheet name twice when referencing a range. Data!$C$2:$C$3000 is sufficient. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Excel 2003 - SPB" wrote in message ... I am trying to use names in a sumproduct() The origonal, which works =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(Data!$E$2:Data!$E$3000=$V145),Data!$H$2:Data!$H$3 000+Data!$K$2:Data!$K$3000+Data!$J$2:Data!$J$3000+ Data!$I$2:Data!$I$3000) NEW, does NOT work =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(DPilot=$V145),DDay+DNight+DNVG+DIFR) DDay = OFFSET(Data!$C$2,0,0,COUNTA(Data!$A:$A),1) [used $a:$a to extend to bottom of data filled] - Dnight,DNVG,Difr similar but use different column on same table I also tried DDay = offset(database,0,21) as the "refers to" named range Any thoughts, I was trying to make it easier to read Thanks SPB Office 2007 |
Sumproduct & Named range
It's because the ranges are of unequal size, you can't have 3 (or one)
dynamic range unless they are of the same size as the other ranges (C and E), you can easily test that by changing the formula that works, where I change the range in column K to 30000 and it returns the error. =SUMPRODUCT(--(TEXT(Data!$C$2:$C$3000,"mmyy")=TEXT($J$4,"mmyy")) ,--(Data!$E$2:$E$3000=$V145),Data!$H$2:$H$3000+Data!$ K$2:$K$30000+Data!$J$2:$J$3000+Data!$I$2:$I$3000) So if you have dynamic ranges based on a count all ranges need to be dynamic and based on the same count or else you will get an error except those times they happen to be of equal size -- Regards, Peo Sjoblom "Excel 2007 - SPB" wrote in message ... I get a #N/A result "Peo Sjoblom" wrote: What does "does not work" means? -- Regards, Peo Sjoblom "Excel 2003 - SPB" wrote in message ... I am running 2007 Thanks for the sheet naming twice tip any thoughts on the "Named" ranges SPB "RagDyeR" wrote: Unless you're using XL07, Sumproduct and array formulas *cannot* reference ENTIRE columns (A:A). Also, there's really no reason to include the sheet name twice when referencing a range. Data!$C$2:$C$3000 is sufficient. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Excel 2003 - SPB" wrote in message ... I am trying to use names in a sumproduct() The origonal, which works =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(Data!$E$2:Data!$E$3000=$V145),Data!$H$2:Data!$H$3 000+Data!$K$2:Data!$K$3000+Data!$J$2:Data!$J$3000+ Data!$I$2:Data!$I$3000) NEW, does NOT work =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(DPilot=$V145),DDay+DNight+DNVG+DIFR) DDay = OFFSET(Data!$C$2,0,0,COUNTA(Data!$A:$A),1) [used $a:$a to extend to bottom of data filled] - Dnight,DNVG,Difr similar but use different column on same table I also tried DDay = offset(database,0,21) as the "refers to" named range Any thoughts, I was trying to make it easier to read Thanks SPB Office 2007 |
Sumproduct & Named range
Thank you, I had one of the ranges looking at a smaller foot print, work like a charm SPB "Peo Sjoblom" wrote: It's because the ranges are of unequal size, you can't have 3 (or one) dynamic range unless they are of the same size as the other ranges (C and E), you can easily test that by changing the formula that works, where I change the range in column K to 30000 and it returns the error. =SUMPRODUCT(--(TEXT(Data!$C$2:$C$3000,"mmyy")=TEXT($J$4,"mmyy")) ,--(Data!$E$2:$E$3000=$V145),Data!$H$2:$H$3000+Data!$ K$2:$K$30000+Data!$J$2:$J$3000+Data!$I$2:$I$3000) So if you have dynamic ranges based on a count all ranges need to be dynamic and based on the same count or else you will get an error except those times they happen to be of equal size -- Regards, Peo Sjoblom "Excel 2007 - SPB" wrote in message ... I get a #N/A result "Peo Sjoblom" wrote: What does "does not work" means? -- Regards, Peo Sjoblom "Excel 2003 - SPB" wrote in message ... I am running 2007 Thanks for the sheet naming twice tip any thoughts on the "Named" ranges SPB "RagDyeR" wrote: Unless you're using XL07, Sumproduct and array formulas *cannot* reference ENTIRE columns (A:A). Also, there's really no reason to include the sheet name twice when referencing a range. Data!$C$2:$C$3000 is sufficient. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Excel 2003 - SPB" wrote in message ... I am trying to use names in a sumproduct() The origonal, which works =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(Data!$E$2:Data!$E$3000=$V145),Data!$H$2:Data!$H$3 000+Data!$K$2:Data!$K$3000+Data!$J$2:Data!$J$3000+ Data!$I$2:Data!$I$3000) NEW, does NOT work =SUMPRODUCT(--(TEXT(Data!$C$2:Data!$C$3000,"mmyy")=TEXT($J$4,"mm yy")),--(DPilot=$V145),DDay+DNight+DNVG+DIFR) DDay = OFFSET(Data!$C$2,0,0,COUNTA(Data!$A:$A),1) [used $a:$a to extend to bottom of data filled] - Dnight,DNVG,Difr similar but use different column on same table I also tried DDay = offset(database,0,21) as the "refers to" named range Any thoughts, I was trying to make it easier to read Thanks SPB Office 2007 |
| All times are GMT +1. The time now is 07:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com