Sumproduct & Named range

Guest · Sep 8, 2007

I am trying to use names in a sumproduct()

The origonal, which works

=SUMPRODUCT(--(TEXT(Data!$C$2

ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(Data!$E$2

ata!$E$3000=$V145),Data!$H$2

ata!$H$3000+Data!$K$2

ata!$K$3000+Data!$J$2

ata!$J$3000+Data!$I$2

ata!$I$3000)

NEW, does NOT work
=SUMPRODUCT(--(TEXT(Data!$C$2

ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(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

RagDyeR · Sep 8, 2007

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!
=====================================================

I am trying to use names in a sumproduct()

The origonal, which works

=SUMPRODUCT(--(TEXT(Data!$C$2

ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(Data!$E$2

ata!$E$3000=$V145),Data!$H$2

ata!$H$3000+Data!$K$2

ata!$K$3000+Data!$J$2

ata!$J$3000+Data!$I$2

ata!$I$3000)

NEW, does NOT work
=SUMPRODUCT(--(TEXT(Data!$C$2

ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(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

Guest · Sep 9, 2007

I am running 2007
Thanks for the sheet naming twice tip
any thoughts on the "Named" ranges

SPB

Peo Sjoblom · Sep 9, 2007

What does "does not work" means?

--

Regards,

Peo Sjoblom

Excel 2003 - SPB said:
I am running 2007
Thanks for the sheet naming twice tip
any thoughts on the "Named" ranges

SPB

RagDyeR said:

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!
=====================================================

message
I am trying to use names in a sumproduct()

The origonal, which works

=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(Data!$E$2ata!$E$3000=$V145),Data!$H$2ata!$H$3000+Data!$K$2ata!$K$3000+Data!$J$2ata!$J$3000+Data!$I$2ata!$I$3000)

NEW, does NOT work
=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(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

Click to expand...

Guest · Sep 9, 2007

I get a #N/A result

Peo Sjoblom said:
What does "does not work" means?

--

Regards,

Peo Sjoblom

Excel 2003 - SPB said:

I am running 2007
Thanks for the sheet naming twice tip
any thoughts on the "Named" ranges

SPB

RagDyeR said:

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!
=====================================================

message
I am trying to use names in a sumproduct()

The origonal, which works

=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(Data!$E$2ata!$E$3000=$V145),Data!$H$2ata!$H$3000+Data!$K$2ata!$K$3000+Data!$J$2ata!$J$3000+Data!$I$2ata!$I$3000)

NEW, does NOT work
=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(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

Click to expand...

Click to expand...

Peo Sjoblom · Sep 9, 2007

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 said:
I get a #N/A result

Peo Sjoblom said:

What does "does not work" means?

--

Regards,

Peo Sjoblom

message

I am running 2007
Thanks for the sheet naming twice tip
any thoughts on the "Named" ranges

SPB

:

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!
=====================================================

message
I am trying to use names in a sumproduct()

The origonal, which works

=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(Data!$E$2ata!$E$3000=$V145),Data!$H$2ata!$H$3000+Data!$K$2ata!$K$3000+Data!$J$2ata!$J$3000+Data!$I$2ata!$I$3000)

NEW, does NOT work
=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(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

Click to expand...

Click to expand...

Guest · Sep 10, 2007

Thank you, I had one of the ranges looking at a smaller foot print, work
like a charm

SPB

Peo Sjoblom said:
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 said:

I get a #N/A result

Peo Sjoblom said:

What does "does not work" means?

--

Regards,

Peo Sjoblom

message
I am running 2007
Thanks for the sheet naming twice tip
any thoughts on the "Named" ranges

SPB

:

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!
=====================================================

message
I am trying to use names in a sumproduct()

The origonal, which works

=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(Data!$E$2ata!$E$3000=$V145),Data!$H$2ata!$H$3000+Data!$K$2ata!$K$3000+Data!$J$2ata!$J$3000+Data!$I$2ata!$I$3000)

NEW, does NOT work
=SUMPRODUCT(--(TEXT(Data!$C$2ata!$C$3000,"mmyy")=TEXT($J$4,"mmyy")),--(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

Click to expand...

Click to expand...

Sumproduct & Named range

Guest

RagDyeR

Guest

Peo Sjoblom

Guest

Peo Sjoblom

Guest