View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Excel 2003 - SPB Excel 2003 - SPB is offline
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