SUMPRODUCT can use (dynamic) named ranges and is not entered as an array
formula .. just Enter.

SUMPRODUCT(--(Range1="Functional"),--(Range2=A3))

Range1 and Range2 are dynamic ranges BUT must be same size. Or simple set to
total range (A1:A65500)


"George" wrote:

Everyone, I've been reading up about this issue recently and it appears that
I can't use whole columns in any formula I've so far tried. Can someone
confirm this for me for definite please? The formulas I've tried so far
include the SUMPRODUCT and SUM(IF( as follows:

=SUM(IF('ES Allocation'!$A3:$A500="Functional", IF('ES
Allocation'!$E3:$E500=$A3,1,0), 0))
=SUMPRODUCT(--('ES Allocation'!$A3:$A500="Functional"),--('ES
Allocation'!$E3:$E500=A3))

Both are array formulas and only work when I've specified the range rather
than use $A:$A - I've seen somewhere that this limitation is removed from
Office 2007 but in other versions these formulas (with columns) return #NUM!

*IS* there any method in Office 2003 where I might be able to fudge
something together to calculate these two values. I don't really want to set
aside another cell to store temporary values so at the moment I think I'm
down to using these formulas with cell references as the range :-(

Is there a formula that would accept a named range and then still be able to
use column references or am I better sticking with the "imperfect" solution I
already have :-)

Thanks