Sumproduct Help!
 

Hi
Thanks for looking

Below is the formula i'm using and it works just fine.
=SUMPRODUCT(--(YEAR(Serial!$G$2:$G$3222)=$B$1),--(MONTH(Serial!$G$2:$G$3222)=D2)

What i'm trying to do is select the whole column (G) as below
=SUMPRODUCT(--(YEAR(Serial!G:G)=$B$1),--(MONTH(Serial!G:G)=D2)

I just can't get this to work, any ideas

Cheers
Peter (UK)

Sumproduct Help!
 

On Sun, 15 Mar 2009 08:00:49 -0000, "Peter" wrote:

Hi
Thanks for looking

Below is the formula i'm using and it works just fine.
=SUMPRODUCT(--(YEAR(Serial!$G$2:$G$3222)=$B$1),--(MONTH(Serial!$G$2:$G$3222)=D2)

What i'm trying to do is select the whole column (G) as below
=SUMPRODUCT(--(YEAR(Serial!G:G)=$B$1),--(MONTH(Serial!G:G)=D2)

I just can't get this to work, any ideas

Cheers
Peter (UK)




Do you use Excel 2003?
I think you will need Excel 2007 in order to use "whole column
reference".

Also, have a look under "Whole Column and Row References" at this page
http://msdn.microsoft.com/en-us/library/aa730921.aspx

Hope this helps / Lars-Åke

Sumproduct Help!
 

Peter;270369 Wrote:
Hi
Thanks for looking

Below is the formula i'm using and it works just fine.
=SUMPRODUCT(--(YEAR(Serial!$G$2:$G$3222)=$B$1),--(MONTH(Serial!$G$2:$G$3222)=D2)

What i'm trying to do is select the whole column (G) as below
=SUMPRODUCT(--(YEAR(Serial!G:G)=$B$1),--(MONTH(Serial!G:G)=D2)

I just can't get this to work, any ideas

Cheers
Peter (UK)
Hi,

Unless you have 2007 you can't specify the whole column with
SUMPRODUCT, but you can use G1:G65536


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.thecodecage.com/forumz/member.php?userid=111
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75374

Sumproduct Help! Thank You
 

"oldchippy" wrote in message
...

Peter;270369 Wrote:
Hi
Thanks for looking

Below is the formula i'm using and it works just fine.
=SUMPRODUCT(--(YEAR(Serial!$G$2:$G$3222)=$B$1),--(MONTH(Serial!$G$2:$G$3222)=D2)

What i'm trying to do is select the whole column (G) as below
=SUMPRODUCT(--(YEAR(Serial!G:G)=$B$1),--(MONTH(Serial!G:G)=D2)

I just can't get this to work, any ideas

Cheers
Peter (UK)
Hi,

Unless you have 2007 you can't specify the whole column with
SUMPRODUCT, but you can use G1:G65536


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile:
http://www.thecodecage.com/forumz/member.php?userid=111
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75374


Cheers Guys for your replies

I don't fancy upgrading to 2007 just yet, i'll use the G1:G65536 work around

Tanks again

Peter (UK)

Sumproduct Help! Thank You
 

Hi Peter

Whilst Sumproduct is a very clever formula, it is not as efficient as Sumif,
which only utilises the used range, no matter what range you give it.
Giving almost the whole column, G1:G65535 (you cannot use 65536), will give
you a big performance hit, as Sumproduct carries out it's work on lots of
empty cells.

You will be much better off by creating a Dynamic range for your data, which
will grow (or shrink) as you add more data.

InsertNameDefine
Name myData
Refers to =$G$1:INDEX($G:$G,COUNTA($G:$G))

=SUMPRODUCT(--(YEAR(myData)=$B$1),--(MONTH(myData)=D2)


--
Regards
Roger Govier

"Peter" wrote in message
...
"oldchippy" wrote in message
...

Peter;270369 Wrote:
Hi
Thanks for looking

Below is the formula i'm using and it works just fine.
=SUMPRODUCT(--(YEAR(Serial!$G$2:$G$3222)=$B$1),--(MONTH(Serial!$G$2:$G$3222)=D2)

What i'm trying to do is select the whole column (G) as below
=SUMPRODUCT(--(YEAR(Serial!G:G)=$B$1),--(MONTH(Serial!G:G)=D2)

I just can't get this to work, any ideas

Cheers
Peter (UK)
Hi,

Unless you have 2007 you can't specify the whole column with
SUMPRODUCT, but you can use G1:G65536


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile:
http://www.thecodecage.com/forumz/member.php?userid=111
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=75374


Cheers Guys for your replies

I don't fancy upgrading to 2007 just yet, i'll use the G1:G65536 work
around

Tanks again

Peter (UK)

"Dynamic Range" Thank You
 

Cheers Roger

Your right "big performance hit" when i open my sheet, i have to wait while
it recalculates.

I'll try the Dynamic range below as you suggested. I've only used the
"InsertNameDefine" a couple of times and that was more luck that
judgement.

Thanks

Peter (UK)


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Peter

Whilst Sumproduct is a very clever formula, it is not as efficient as
Sumif, which only utilises the used range, no matter what range you give
it.
Giving almost the whole column, G1:G65535 (you cannot use 65536), will
give you a big performance hit, as Sumproduct carries out it's work on
lots of empty cells.

You will be much better off by creating a Dynamic range for your data,
which will grow (or shrink) as you add more data.

InsertNameDefine
Name myData
Refers to =$G$1:INDEX($G:$G,COUNTA($G:$G))

=SUMPRODUCT(--(YEAR(myData)=$B$1),--(MONTH(myData)=D2)


--
Regards
Roger Govier

"Peter" wrote in message
...
"oldchippy" wrote in message
...

Peter;270369 Wrote:
Hi
Thanks for looking

Below is the formula i'm using and it works just fine.
=SUMPRODUCT(--(YEAR(Serial!$G$2:$G$3222)=$B$1),--(MONTH(Serial!$G$2:$G$3222)=D2)

What i'm trying to do is select the whole column (G) as below
=SUMPRODUCT(--(YEAR(Serial!G:G)=$B$1),--(MONTH(Serial!G:G)=D2)

I just can't get this to work, any ideas

Cheers
Peter (UK)
Hi,

Unless you have 2007 you can't specify the whole column with
SUMPRODUCT, but you can use G1:G65536


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile:
http://www.thecodecage.com/forumz/member.php?userid=111
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=75374


Cheers Guys for your replies

I don't fancy upgrading to 2007 just yet, i'll use the G1:G65536 work
around

Tanks again

Peter (UK)

"Dynamic Range" Thank You
 

Hi,

Ctrl+F3 is the shortcut key for the Insert, Name, Define command.

Using G1:G65536 is not a workaround for your problem as Rodger was
indicating because Excel will just convert that to G:G and your formula will
fail. And even if you do "trick", mother nature doesn't like tricks... so if
you use INDIRECT("G1:G65536") but Excel will still be unhappy.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Peter" wrote:

Cheers Roger

Your right "big performance hit" when i open my sheet, i have to wait while
it recalculates.

I'll try the Dynamic range below as you suggested. I've only used the
"InsertNameDefine" a couple of times and that was more luck that
judgement.

Thanks

Peter (UK)


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Peter

Whilst Sumproduct is a very clever formula, it is not as efficient as
Sumif, which only utilises the used range, no matter what range you give
it.
Giving almost the whole column, G1:G65535 (you cannot use 65536), will
give you a big performance hit, as Sumproduct carries out it's work on
lots of empty cells.

You will be much better off by creating a Dynamic range for your data,
which will grow (or shrink) as you add more data.

InsertNameDefine
Name myData
Refers to =$G$1:INDEX($G:$G,COUNTA($G:$G))

=SUMPRODUCT(--(YEAR(myData)=$B$1),--(MONTH(myData)=D2)


--
Regards
Roger Govier

"Peter" wrote in message
...
"oldchippy" wrote in message
...

Peter;270369 Wrote:
Hi
Thanks for looking

Below is the formula i'm using and it works just fine.
=SUMPRODUCT(--(YEAR(Serial!$G$2:$G$3222)=$B$1),--(MONTH(Serial!$G$2:$G$3222)=D2)

What i'm trying to do is select the whole column (G) as below
=SUMPRODUCT(--(YEAR(Serial!G:G)=$B$1),--(MONTH(Serial!G:G)=D2)

I just can't get this to work, any ideas

Cheers
Peter (UK)
Hi,

Unless you have 2007 you can't specify the whole column with
SUMPRODUCT, but you can use G1:G65536


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile:
http://www.thecodecage.com/forumz/member.php?userid=111
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=75374


Cheers Guys for your replies

I don't fancy upgrading to 2007 just yet, i'll use the G1:G65536 work
around

Tanks again

Peter (UK)