Sum function based on Date recognition

[Cammy]

Hello there, I am trying to do a vlookup type function which will recognise
dates within a range (ie per month) and then do a sum function on another
column. Is it possible to do this, or do I need to write a macro? If I need a
macro how would I go about formuling the code?

 

[Ardus Petus]

Say you have dates in A1:A10, and amounts in B1:B10

You can write:
=SUMPRODUCT((MONTH(A1:A10)=7)*B1:B10)

to get the summation of all amounts pertaining to the month of july

HTH

 

[Max]

Hello there, I am trying to do a vlookup type function which will recognise
dates within a range (ie per month) and then do a sum function on another
column. Is it possible to do this, or do I need to write a macro? If I need a
macro how would I go about formuling the code?

Try something like this in say, C1:
=SUMPRODUCT(($A$2:$A$100>= -- "1-Jun-2006")*($A$2:$A$100<= --
"15-Jun-2006"),$B$2:$B$100)

assuming real dates are within $A$2:$A$100,
with values to be summed in $B$2:$B$100

Adapt to suit, but note that entire col references, eg: A:A, B:B, cannot be
used in SUMPRODUCT

 

[Cammy]

Thanks that is exactly what I needed.

Try something like this in say, C1:
=SUMPRODUCT(($A$2:$A$100>= -- "1-Jun-2006")*($A$2:$A$100<= --
"15-Jun-2006"),$B$2:$B$100)

assuming real dates are within $A$2:$A$100,
with values to be summed in $B$2:$B$100

Adapt to suit, but note that entire col references, eg: A:A, B:B, cannot be
used in SUMPRODUCT

 

[Max]

Thanks that is exactly what I needed.

Glad to hear that, Cammy !
Thanks for the feedback ..