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