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
|