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

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

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

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

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