please help a desperate financial analyst out! i have a data table with
three columns: date, num of shares, and dividend per share. i want to create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have an inputs
section where you type in the beginning date and the end date and excel
performs a sumproduct for the date range specified. excel's built in lookup
formulas are not helpful b/c in this model excel needs to reference all dates
within the specified date range and write the corresponding cash flows in an
output sheet. can anyone help?

Thanks!

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100=E1),--(A1:A100<=F1),B1:B100,C1:C100)


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
please help a desperate financial analyst out! i have a data table with
three columns: date, num of shares, and dividend per share. i want to
create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have an
inputs
section where you type in the beginning date and the end date and excel
performs a sumproduct for the date range specified. excel's built in
lookup
formulas are not helpful b/c in this model excel needs to reference all
dates
within the specified date range and write the corresponding cash flows in
an
output sheet. can anyone help?

Thanks!

Thanks Valko,

What if I want to instruct excel to output in a separate worksheet a column
with the dates in the range and the corresponding dividends in that date
range?

Example of data table (semi colon denotes new column):

Date ; Dividends Per Share

6/01/03 ; 1.00
7/01/03 ; 1.25
8/01/03 ; 1.30
9/01/03 ; 1.32
10/01/03 ; 1.30

beg date = 6/1/03
end date = 9/1/03

i would like excel to output the following in a new worksheet:

6/1/03 ; 1.00
7/1/03 ; 1.25
8/1/03 ; 1.30
9/1/03 ; 1.32

can you give me a function or set of instructions that tells excel to
perform this computation?

thanks,

Max

"T. Valko" wrote:

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100=E1),--(A1:A100<=F1),B1:B100,C1:C100)


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
please help a desperate financial analyst out! i have a data table with
three columns: date, num of shares, and dividend per share. i want to
create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have an
inputs
section where you type in the beginning date and the end date and excel
performs a sumproduct for the date range specified. excel's built in
lookup
formulas are not helpful b/c in this model excel needs to reference all
dates
within the specified date range and write the corresponding cash flows in
an
output sheet. can anyone help?

Thanks!

Assuming the dates are all unique as is demonstrated in your sample data.

Dates = Sheet1!A2:A10
Div = Sheet1!B2:B10
Start = Sheet2!A2
End = Sheet2!B2

Enter this array formula** on Sheet2 C2:

=IF(ROWS(C$2:C2)<=SUM((dates=start)*(dates<=end)) ,SMALL(IF((dates=start)*(dates<=end),date),ROWS(C $2:C2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula on Sheet2 D2:

=IF(C2="","",SUMIF(dates,C2,div))

Select both C2 and D2 and copy down until you get blanks.

Format the C2:Cn as DATE


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Thanks Valko,

What if I want to instruct excel to output in a separate worksheet a
column
with the dates in the range and the corresponding dividends in that date
range?

Example of data table (semi colon denotes new column):

Date ; Dividends Per Share

6/01/03 ; 1.00
7/01/03 ; 1.25
8/01/03 ; 1.30
9/01/03 ; 1.32
10/01/03 ; 1.30

beg date = 6/1/03
end date = 9/1/03

i would like excel to output the following in a new worksheet:

6/1/03 ; 1.00
7/1/03 ; 1.25
8/1/03 ; 1.30
9/1/03 ; 1.32

can you give me a function or set of instructions that tells excel to
perform this computation?

thanks,

Max

"T. Valko" wrote:

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100=E1),--(A1:A100<=F1),B1:B100,C1:C100)


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
please help a desperate financial analyst out! i have a data table
with
three columns: date, num of shares, and dividend per share. i want to
create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have an
inputs
section where you type in the beginning date and the end date and excel
performs a sumproduct for the date range specified. excel's built in
lookup
formulas are not helpful b/c in this model excel needs to reference all
dates
within the specified date range and write the corresponding cash flows
in
an
output sheet. can anyone help?

Thanks!

Valko

Thanks again for helping me out! Quick question: for some reason the formula
is not working and I think it may have to do with a typo in the fomula (or so
I think!) There is a reference to "date" without an "s" in the SMALL
section of the array function and I honestly don't know the purpose of this
term. Could you double check the array function and let me know if there are
any typos?

Thanks!!!

Max



"T. Valko" wrote:

Assuming the dates are all unique as is demonstrated in your sample data.

Dates = Sheet1!A2:A10
Div = Sheet1!B2:B10
Start = Sheet2!A2
End = Sheet2!B2

Enter this array formula** on Sheet2 C2:

=IF(ROWS(C$2:C2)<=SUM((dates=start)*(dates<=end)) ,SMALL(IF((dates=start)*(dates<=end),date),ROWS(C $2:C2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula on Sheet2 D2:

=IF(C2="","",SUMIF(dates,C2,div))

Select both C2 and D2 and copy down until you get blanks.

Format the C2:Cn as DATE


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Thanks Valko,

What if I want to instruct excel to output in a separate worksheet a
column
with the dates in the range and the corresponding dividends in that date
range?

Example of data table (semi colon denotes new column):

Date ; Dividends Per Share

6/01/03 ; 1.00
7/01/03 ; 1.25
8/01/03 ; 1.30
9/01/03 ; 1.32
10/01/03 ; 1.30

beg date = 6/1/03
end date = 9/1/03

i would like excel to output the following in a new worksheet:

6/1/03 ; 1.00
7/1/03 ; 1.25
8/1/03 ; 1.30
9/1/03 ; 1.32

can you give me a function or set of instructions that tells excel to
perform this computation?

thanks,

Max

"T. Valko" wrote:

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100=E1),--(A1:A100<=F1),B1:B100,C1:C100)


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
please help a desperate financial analyst out! i have a data table
with
three columns: date, num of shares, and dividend per share. i want to
create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have an
inputs
section where you type in the beginning date and the end date and excel
performs a sumproduct for the date range specified. excel's built in
lookup
formulas are not helpful b/c in this model excel needs to reference all
dates
within the specified date range and write the corresponding cash flows
in
an
output sheet. can anyone help?

Thanks!

for some reason the formula is not working and
I think it may have to do with a typo

Ahh, yes, you are correct. There is a typo with a missing "s".

I honestly don't know the purpose of this term.

In the formulas, "dates", "div", "start" and "end" are just placeholders for
your actual range references. Some folks think formulas written this way are
easier to read. Personally, I prefer to use the actual ranges references.

Just replace those placeholder terms with your actual range references and
don't forget to array enter the "long" formula!

--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Valko

Thanks again for helping me out! Quick question: for some reason the
formula
is not working and I think it may have to do with a typo in the fomula (or
so
I think!) There is a reference to "date" without an "s" in the SMALL
section of the array function and I honestly don't know the purpose of
this
term. Could you double check the array function and let me know if there
are
any typos?

Thanks!!!

Max



"T. Valko" wrote:

Assuming the dates are all unique as is demonstrated in your sample data.

Dates = Sheet1!A2:A10
Div = Sheet1!B2:B10
Start = Sheet2!A2
End = Sheet2!B2

Enter this array formula** on Sheet2 C2:

=IF(ROWS(C$2:C2)<=SUM((dates=start)*(dates<=end)) ,SMALL(IF((dates=start)*(dates<=end),date),ROWS(C $2:C2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula on Sheet2 D2:

=IF(C2="","",SUMIF(dates,C2,div))

Select both C2 and D2 and copy down until you get blanks.

Format the C2:Cn as DATE


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Thanks Valko,

What if I want to instruct excel to output in a separate worksheet a
column
with the dates in the range and the corresponding dividends in that
date
range?

Example of data table (semi colon denotes new column):

Date ; Dividends Per Share

6/01/03 ; 1.00
7/01/03 ; 1.25
8/01/03 ; 1.30
9/01/03 ; 1.32
10/01/03 ; 1.30

beg date = 6/1/03
end date = 9/1/03

i would like excel to output the following in a new worksheet:

6/1/03 ; 1.00
7/1/03 ; 1.25
8/1/03 ; 1.30
9/1/03 ; 1.32

can you give me a function or set of instructions that tells excel to
perform this computation?

thanks,

Max

"T. Valko" wrote:

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100=E1),--(A1:A100<=F1),B1:B100,C1:C100)


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
please help a desperate financial analyst out! i have a data table
with
three columns: date, num of shares, and dividend per share. i want
to
create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have
an
inputs
section where you type in the beginning date and the end date and
excel
performs a sumproduct for the date range specified. excel's built in
lookup
formulas are not helpful b/c in this model excel needs to reference
all
dates
within the specified date range and write the corresponding cash
flows
in
an
output sheet. can anyone help?

Thanks!

Thank you my friend!

Best regards,

Max

"T. Valko" wrote:

for some reason the formula is not working and
I think it may have to do with a typo

Ahh, yes, you are correct. There is a typo with a missing "s".

I honestly don't know the purpose of this term.

In the formulas, "dates", "div", "start" and "end" are just placeholders for
your actual range references. Some folks think formulas written this way are
easier to read. Personally, I prefer to use the actual ranges references.

Just replace those placeholder terms with your actual range references and
don't forget to array enter the "long" formula!

--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Valko

Thanks again for helping me out! Quick question: for some reason the
formula
is not working and I think it may have to do with a typo in the fomula (or
so
I think!) There is a reference to "date" without an "s" in the SMALL
section of the array function and I honestly don't know the purpose of
this
term. Could you double check the array function and let me know if there
are
any typos?

Thanks!!!

Max



"T. Valko" wrote:

Assuming the dates are all unique as is demonstrated in your sample data.

Dates = Sheet1!A2:A10
Div = Sheet1!B2:B10
Start = Sheet2!A2
End = Sheet2!B2

Enter this array formula** on Sheet2 C2:

=IF(ROWS(C$2:C2)<=SUM((dates=start)*(dates<=end)) ,SMALL(IF((dates=start)*(dates<=end),date),ROWS(C $2:C2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula on Sheet2 D2:

=IF(C2="","",SUMIF(dates,C2,div))

Select both C2 and D2 and copy down until you get blanks.

Format the C2:Cn as DATE


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Thanks Valko,

What if I want to instruct excel to output in a separate worksheet a
column
with the dates in the range and the corresponding dividends in that
date
range?

Example of data table (semi colon denotes new column):

Date ; Dividends Per Share

6/01/03 ; 1.00
7/01/03 ; 1.25
8/01/03 ; 1.30
9/01/03 ; 1.32
10/01/03 ; 1.30

beg date = 6/1/03
end date = 9/1/03

i would like excel to output the following in a new worksheet:

6/1/03 ; 1.00
7/1/03 ; 1.25
8/1/03 ; 1.30
9/1/03 ; 1.32

can you give me a function or set of instructions that tells excel to
perform this computation?

thanks,

Max

"T. Valko" wrote:

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100=E1),--(A1:A100<=F1),B1:B100,C1:C100)


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
please help a desperate financial analyst out! i have a data table
with
three columns: date, num of shares, and dividend per share. i want
to
create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have
an
inputs
section where you type in the beginning date and the end date and
excel
performs a sumproduct for the date range specified. excel's built in
lookup
formulas are not helpful b/c in this model excel needs to reference
all
dates
within the specified date range and write the corresponding cash
flows
in
an
output sheet. can anyone help?

Thanks!

You're welcome!

--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Thank you my friend!

Best regards,

Max

"T. Valko" wrote:

for some reason the formula is not working and
I think it may have to do with a typo

Ahh, yes, you are correct. There is a typo with a missing "s".

I honestly don't know the purpose of this term.

In the formulas, "dates", "div", "start" and "end" are just placeholders
for
your actual range references. Some folks think formulas written this way
are
easier to read. Personally, I prefer to use the actual ranges references.

Just replace those placeholder terms with your actual range references
and
don't forget to array enter the "long" formula!

--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Valko

Thanks again for helping me out! Quick question: for some reason the
formula
is not working and I think it may have to do with a typo in the fomula
(or
so
I think!) There is a reference to "date" without an "s" in the SMALL
section of the array function and I honestly don't know the purpose of
this
term. Could you double check the array function and let me know if
there
are
any typos?

Thanks!!!

Max



"T. Valko" wrote:

Assuming the dates are all unique as is demonstrated in your sample
data.

Dates = Sheet1!A2:A10
Div = Sheet1!B2:B10
Start = Sheet2!A2
End = Sheet2!B2

Enter this array formula** on Sheet2 C2:

=IF(ROWS(C$2:C2)<=SUM((dates=start)*(dates<=end)) ,SMALL(IF((dates=start)*(dates<=end),date),ROWS(C $2:C2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula on Sheet2 D2:

=IF(C2="","",SUMIF(dates,C2,div))

Select both C2 and D2 and copy down until you get blanks.

Format the C2:Cn as DATE


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in message
...
Thanks Valko,

What if I want to instruct excel to output in a separate worksheet a
column
with the dates in the range and the corresponding dividends in that
date
range?

Example of data table (semi colon denotes new column):

Date ; Dividends Per Share

6/01/03 ; 1.00
7/01/03 ; 1.25
8/01/03 ; 1.30
9/01/03 ; 1.32
10/01/03 ; 1.30

beg date = 6/1/03
end date = 9/1/03

i would like excel to output the following in a new worksheet:

6/1/03 ; 1.00
7/1/03 ; 1.25
8/1/03 ; 1.30
9/1/03 ; 1.32

can you give me a function or set of instructions that tells excel
to
perform this computation?

thanks,

Max

"T. Valko" wrote:

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100=E1),--(A1:A100<=F1),B1:B100,C1:C100)


--
Biff
Microsoft Excel MVP


"Max98Perez" wrote in
message
...
please help a desperate financial analyst out! i have a data
table
with
three columns: date, num of shares, and dividend per share. i
want
to
create
a simple calculator that performs a sumproduct of num_shares
times
dividend_per_share for a specified date range. the idea is to
have
an
inputs
section where you type in the beginning date and the end date and
excel
performs a sumproduct for the date range specified. excel's built
in
lookup
formulas are not helpful b/c in this model excel needs to
reference
all
dates
within the specified date range and write the corresponding cash
flows
in
an
output sheet. can anyone help?

Thanks!