Hi,

I need a function that scans a sheet for different identifiers and sums up the attributes of that variable within a specific date range ( from today till a specified date).

A sample list is below;

DATE DIV RIC
9/19/2011 3.6 ABAN.NS
9/12/2012 3.6 ABAN.NS
9/19/2013 4 ABAN.NS
5/3/2010 2 ABB.NS
5/2/2011 2 ABB.NS
5/2/2012 3 ABB.NS
4/29/2013 3 ABB.NS
7/17/2012 10 ABBP.NS
7/1/2013 10 ABBP.NS
9/16/2010 4 ABGS.NS
9/15/2011 4 ABGS.NS
9/13/2012 4 ABGS.NS
9/17/2013 4 ABGS.NS
9/17/2014 4 ABGS.NS
9/17/2015 4 ABGS.NS

For example, for for ABAN.NS stock, I need to sum up all values in column B whose dates fall between today and a specified date. So if I need all divs of ABAN.NS falling between today and 31-Dec-2012 this year, it should give me 3.6

I am using this specific function but this is not working;

Function dividends(ric As String, start_date As Date, end_date As Date) As Long

Dim i As Integer
Dim temp As Integer

dividends = 0

Sheets("Dividend").Activate
i = Sheets("Dividend").Range("A:A").Cells.SpecialCells (xlCellTypeConstants).Count

For temp = 2 To i
If Sheets("Dividend").Cells("F" & i) = ric And Sheets("Dividend").Range("B" & i).Value startdate And Sheets("Dividend").Range("B" & i).Value <= enddate Then

dividends = dividends + Sheets("Dividend").Range("C" & i).Value
End If
Next temp

End Function

The exact reference may not match as I have trimmed many columns in the data which are not necessary.

hi,

=SUMPRODUCT(--(A2:A16TODAY())*(A2:A16<DATE(2012,12,31))*(C2:C16 ="ABAN.NS")*(B2:B16))

--
isabelle



Le 2012-08-23 13:07, Longgamma a écrit :

Hi,

I need a function that scans a sheet for different identifiers and sums
up the attributes of that variable within a specific date range ( from
today till a specified date).

A sample list is below;

DATE DIV RIC
9/19/2011 3.6 ABAN.NS
9/12/2012 3.6 ABAN.NS
9/19/2013 4 ABAN.NS
5/3/2010 2 ABB.NS
5/2/2011 2 ABB.NS
5/2/2012 3 ABB.NS
4/29/2013 3 ABB.NS
7/17/2012 10 ABBP.NS
7/1/2013 10 ABBP.NS
9/16/2010 4 ABGS.NS
9/15/2011 4 ABGS.NS
9/13/2012 4 ABGS.NS
9/17/2013 4 ABGS.NS
9/17/2014 4 ABGS.NS
9/17/2015 4 ABGS.NS

For example, for for ABAN.NS stock, I need to sum up all values in
column B whose dates fall between today and a specified date. So if I
need all divs of ABAN.NS falling between today and 31-Dec-2012 this
year, it should give me 3.6

I am using this specific function but this is not working;

Function dividends(ric As String, start_date As Date, end_date As Date)
As Long

Dim i As Integer
Dim temp As Integer

dividends = 0

Sheets("Dividend").Activate
i =
Sheets("Dividend").Range("A:A").Cells.SpecialCells (xlCellTypeConstants).Count

For temp = 2 To i
If Sheets("Dividend").Cells("F" & i) = ric And
Sheets("Dividend").Range("B" & i).Value startdate And
Sheets("Dividend").Range("B" & i).Value <= enddate Then

dividends = dividends + Sheets("Dividend").Range("C" & i).Value
End If
Next temp

End Function

The exact reference may not match as I have trimmed many columns in the
data which are not necessary.