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

Hi,

DSUM will work against an entire column in all versions:

=DSUM(A:B,1,G1:H2)

You can set up as complex a criteria range as you want.
--
Cheers,
Shane Devenshire


"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

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

Shane - thanks for the response. I'm going to check out the DSUM formula now
to see if it's going to help me in my quest!

Great answer - will hopefully remember to come back on and say thanks if it
works :-)



"ShaneDevenshire" wrote:

> Hi,
>
> DSUM will work against an entire column in all versions:
>
> =DSUM(A:B,1,G1:H2)
>
> You can set up as complex a criteria range as you want.
> --
> Cheers,
> Shane Devenshire
>
>
> "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

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

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

Shane - you'll see Toppers' response no doubt. That's the version I've got
working straight away. I'm going to investigate the DSUM function a little
more though as it's a lot neater and easier to maintain (I'll hopefully be
giving someone else the job of maintaining the sheet) :-)

Thanks for the response, but this time Toppers get the Answer as it's
working straight away and I unfortunately haven't worked out the DSUM yet :-)

"ShaneDevenshire" wrote:

> Hi,
>
> DSUM will work against an entire column in all versions:
>
> =DSUM(A:B,1,G1:H2)
>
> You can set up as complex a criteria range as you want.
> --
> Cheers,
> Shane Devenshire
>
>
> "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

Toppers - thanks for this. Perfect answer to my problem. I knew there must
have been a way around it. I'm still going to investigate the DSUM approach
because of the "neatness" of the solution, but you get the Answer this time
round!

Thanks to both Shane and Toppers who have helped me out enormously!

"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