finding a range of dates to total

D

Danbmarine

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
 
D

Domenic

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!
 
P

pinmaster

Try this:

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

HTH
JG
 
J

JMB

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)
 
P

pinmaster

Another way:

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

HTH
JG
 
Top