Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
I have data in the current format:
Date Amount 1/1/2007 1.00 1/15/2007 2.50 2/2/2007 3.00 I want to be able to reference the whole range but only sum the values that are in January. I have the following formula, however it is returning a 0. =SUMIF('Data Sheet'!A2:A20,and(=1/1/2007,<=2/1/2007),'Data Sheet'!I2:I20). I appreicate the help. Thanks, Scott |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
I want to be able to reference the whole range but only sum the values
that are in January. try one way =SUMPRODUCT((YEAR('Data Sheet'!A2:A20)=2007)*(MONTH('Data Sheet'!A2:A20)=1)*'Data Sheet'!I2:I20) adjust to suit if u want to try.. -- ***** birds of the same feather flock together.. "Scott Halper" wrote: I have data in the current format: Date Amount 1/1/2007 1.00 1/15/2007 2.50 2/2/2007 3.00 I want to be able to reference the whole range but only sum the values that are in January. I have the following formula, however it is returning a 0. =SUMIF('Data Sheet'!A2:A20,and(=1/1/2007,<=2/1/2007),'Data Sheet'!I2:I20). I appreicate the help. Thanks, Scott |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
Thanks.
driller wrote: I want to be able to reference the whole range but only sum the values that are in January. try one way =SUMPRODUCT((YEAR('Data Sheet'!A2:A20)=2007)*(MONTH('Data Sheet'!A2:A20)=1)*'Data Sheet'!I2:I20) adjust to suit if u want to try.. -- ***** birds of the same feather flock together.. "Scott Halper" wrote: I have data in the current format: Date Amount 1/1/2007 1.00 1/15/2007 2.50 2/2/2007 3.00 I want to be able to reference the whole range but only sum the values that are in January. I have the following formula, however it is returning a 0. =SUMIF('Data Sheet'!A2:A20,and(=1/1/2007,<=2/1/2007),'Data Sheet'!I2:I20). I appreicate the help. Thanks, Scott |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
This is really the same but a shorter approach if you're only worried about
the month: =SUMPRODUCT((MONTH(A1:A47)=1)*B1:B47) or =SUM((MONTH(A1:A47)=1)*B1:B47) array entered. -- Cheers, Shane Devenshire "Scott Halper" wrote: Thanks. driller wrote: I want to be able to reference the whole range but only sum the values that are in January. try one way =SUMPRODUCT((YEAR('Data Sheet'!A2:A20)=2007)*(MONTH('Data Sheet'!A2:A20)=1)*'Data Sheet'!I2:I20) adjust to suit if u want to try.. -- ***** birds of the same feather flock together.. "Scott Halper" wrote: I have data in the current format: Date Amount 1/1/2007 1.00 1/15/2007 2.50 2/2/2007 3.00 I want to be able to reference the whole range but only sum the values that are in January. I have the following formula, however it is returning a 0. =SUMIF('Data Sheet'!A2:A20,and(=1/1/2007,<=2/1/2007),'Data Sheet'!I2:I20). I appreicate the help. Thanks, Scott |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
you're welcome
HTH -- ***** birds of the same feather flock together.. "Scott Halper" wrote: Thanks. driller wrote: I want to be able to reference the whole range but only sum the values that are in January. try one way =SUMPRODUCT((YEAR('Data Sheet'!A2:A20)=2007)*(MONTH('Data Sheet'!A2:A20)=1)*'Data Sheet'!I2:I20) adjust to suit if u want to try.. -- ***** birds of the same feather flock together.. "Scott Halper" wrote: I have data in the current format: Date Amount 1/1/2007 1.00 1/15/2007 2.50 2/2/2007 3.00 I want to be able to reference the whole range but only sum the values that are in January. I have the following formula, however it is returning a 0. =SUMIF('Data Sheet'!A2:A20,and(=1/1/2007,<=2/1/2007),'Data Sheet'!I2:I20). I appreicate the help. Thanks, Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the TODAY() function in a SUMIF function | Excel Worksheet Functions | |||
Can I add and IF function to a SUMIF function? | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
SUMIF function | Excel Worksheet Functions |