Sum function based on Date recognition
 

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?

Sum function based on Date recognition
 

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
--
AP

"Cammy" a écrit dans le message de news:
...
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?

Sum function based on Date recognition
 

"Cammy" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum function based on Date recognition
 

Thanks that is exactly what I needed.

"Max" wrote:

"Cammy" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum function based on Date recognition
 

"Cammy" wrote:
Thanks that is exactly what I needed.

Glad to hear that, Cammy !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---