multiple value lookups

  • Thread starter Thread starter Max98Perez
  • Start date Start date
M

Max98Perez

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)
 
Back
Top