Just a quick update on this - I've just realised I could setup two different
ranges for the columns I want. They'll both be the same because they're both
taken from the same subset of data so I should be okay with this approach as
well!



"George" wrote:

Toppers - thanks for the reply. I didn't know this about SUMPRODUCT so am
going to give it a go now. I'd only be using one range and inspecting
different columns in each part of the criteria so am not sure how this will
affect the formula.

Again - if I remember (see reply to Shanes answer) I'll log back on and let
all know the results of my investigations!

Thanks again!

"Toppers" wrote:

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