Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
"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) |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
"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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions |