Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 26
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 26
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default 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





  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default 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








  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct against worksheet vs named range- any speed difference? Keith R Excel Worksheet Functions 1 August 13th 07 04:12 PM
Sumproduct Indirect Named Dynamic Range using Offset Frank Hayes Excel Worksheet Functions 6 May 6th 07 07:32 AM
automatic range - named range give me circular reference... George Thorogood Excel Discussion (Misc queries) 0 February 22nd 07 08:53 PM
Array as a "named range" - formula ok in cells, but error as "named range" tskogstrom Excel Discussion (Misc queries) 11 December 28th 06 05:44 PM
inserting a named range into new cells based on a named cell Peter S. Excel Discussion (Misc queries) 1 June 4th 06 04:53 AM


All times are GMT +1. The time now is 02:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"