Need a little help with a custom function

[Longgamma]

Hi,

I need a function that scans a sheet for different identifiers and sum
up the attributes of that variable within a specific date range ( fro
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 i
column B whose dates fall between > today and a specified date. So if
need all divs of ABAN.NS falling between today and 31-Dec-2012 thi
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 An
Sheets("Dividend").Range("B" & i).Value > startdate An
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 th
data which are not necessary

 

[isabelle]

hi,

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

--
isabelle



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