=SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(P rospects!$H$2:$H
$10000)=3)*(Prospects!$C$2:$C$10000="W"))
that when any records are deleted on the referenced SS named
"Prospects" that the result is the formulas 10,000 end row is reduced
by the number of records deleted. Soon the formulas could point to
nothing?????

If you set up Named ranges, InsertName
Define Fcol
Refers to
=Prospects!$F$2:INDEX(Prospects!$F:$F,COUNTA(Prosp ects!$F:$F))

Repeat for Hcol and Ccol, but keep the CountA part of the formula always
referring to Prospects!$F:$F as this will ensure that the ranges are
always of the same dimension.

The formula then becomes
=SUMPRODUCT((Fcol="Y")*(MONTH(Hcol)=3)*(Ccol="W"))

The size of each "column" will shrink and grow as data is appended or
deleted from the source data but it will always represent the number of
rows that are contiguously filled in what you decide is the "main"
column i.e the one which will have no blanks in its data.
--
Regards

Roger Govier


"DbMstr" wrote in message
oups.com...
Does XL 2007 allow named column ranges in SUMIF or SUMPRODUCT?

Yes it does. But with 1 million rows, maybe not a good idea from a
speed
viewpoint.
Dynamic ranges can be made dynamic in terms of row and column, so I
don't see what your problem is.

Regards
Roger Govier


Welllll it appears that when a formula in 2003 references a fixed
range on another sheet such as:
=SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(P rospects!$H$2:$H
$10000)=3)*(Prospects!$C$2:$C$10000="W"))
that when any records are deleted on the referenced SS named
"Prospects" that the result is the formulas 10,000 end row is reduced
by the number of records deleted. Soon the formulas could point to
nothing?????
I had previously thought that I could use a Named column so this issue
would not exist. Unfortunately SUMPRODUCT does not allow a named
column as it won't accept a complete column.
I now have everything working except one formula but that is another
issue. SUMPRODUCT has turned out to be the solution for most
problems.

Thanks again for all your help.
Dennis