I have a sheet with many dates and I want to search for date ranges in
monthly intervals then total all cells next to the date. for example:

=SUMIF(H:H,DATE(2006,1,1):(2006,1,31),I:I)
for the month of january--however this formula is incorrect.

Does anyone know a correct one to use?

Thanks in advance,
Dan

Try...

=SUMIF(H:H,"="&DATE(2006,1,1),I:I)-SUMIF(H:H,""&DATE(2006,1,31))

or

=SUMPRODUCT(--(H1:H100=DATE(2006,1,1)),--(I1:I100<=DATE(2006,1,31)))

Note that SUMPRODUCT does not accept whole column references.

Hope this helps!

In article ,
"Danbmarine" wrote:

I have a sheet with many dates and I want to search for date ranges in
monthly intervals then total all cells next to the date. for example:

=SUMIF(H:H,DATE(2006,1,1):(2006,1,31),I:I)
for the month of january--however this formula is incorrect.

Does anyone know a correct one to use?

Thanks in advance,
Dan

Try this:

=SUMPRODUCT((H1:H1000=DATE(2006,1,1))*(H1:H1000<= DATE(2006,1,31)),I1:I1000)

HTH
JG

"Danbmarine" wrote:

I have a sheet with many dates and I want to search for date ranges in
monthly intervals then total all cells next to the date. for example:

=SUMIF(H:H,DATE(2006,1,1):(2006,1,31),I:I)
for the month of january--however this formula is incorrect.

Does anyone know a correct one to use?

Thanks in advance,
Dan

Either

=SUMPRODUCT(--(H1:H65535=DATE(2006,1,1)),--(H1:H65535<=DATE(2006,1,31)),I1:I65535)

Or
=SUMIF(H:H,"="&DATE(2006,1,1),I:I) - SUMIF(H:H,""&DATE(2006,1,31),I:I)

"Danbmarine" wrote:

I have a sheet with many dates and I want to search for date ranges in
monthly intervals then total all cells next to the date. for example:

=SUMIF(H:H,DATE(2006,1,1):(2006,1,31),I:I)
for the month of january--however this formula is incorrect.

Does anyone know a correct one to use?

Thanks in advance,
Dan

Another way:

=SUMPRODUCT((MONTH(H1:H1000)=1)*(YEAR(H1:H1000)=20 06),I1:I1000)

HTH
JG

"JMB" wrote:

Either

=SUMPRODUCT(--(H1:H65535=DATE(2006,1,1)),--(H1:H65535<=DATE(2006,1,31)),I1:I65535)

Or
=SUMIF(H:H,"="&DATE(2006,1,1),I:I) - SUMIF(H:H,""&DATE(2006,1,31),I:I)

"Danbmarine" wrote:

I have a sheet with many dates and I want to search for date ranges in
monthly intervals then total all cells next to the date. for example:

=SUMIF(H:H,DATE(2006,1,1):(2006,1,31),I:I)
for the month of january--however this formula is incorrect.

Does anyone know a correct one to use?

Thanks in advance,
Dan