I need to know if this can be done. On sheet 2 I have 3 columns (A,B,C);
date from, date to and interest rate. I need a formula that would allow me
on sheet 1 to input a date in one cell (D8) and have the corresponding
interest rate (as shown on sheet 2) appear in another (F8). I've been
messin with this for weeks and am at my wits end :)

Thanks, Jim

As long as the dates don't overlap you could use

=SUMPRODUCT(--(Sheet2!A2:A200=D8),--(Sheet2!B2:B200<=D8),C2:C200)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim" wrote in message
news:cHH_e.314911$on1.250098@clgrps13...
I need to know if this can be done. On sheet 2 I have 3 columns (A,B,C);
date from, date to and interest rate. I need a formula that would allow me
on sheet 1 to input a date in one cell (D8) and have the corresponding
interest rate (as shown on sheet 2) appear in another (F8). I've been
messin with this for weeks and am at my wits end :)

Thanks, Jim

Didn't work, I tried

=SUMPRODUCT(--(Sheet2!A2:A200=D8),--(Sheet2!B2:B200<=D8),(Sheet2!C2:C200)

didn't work either :(

Thanks, Jim

"Bob Phillips" wrote in message
...
As long as the dates don't overlap you could use

=SUMPRODUCT(--(Sheet2!A2:A200=D8),--(Sheet2!B2:B200<=D8),C2:C200)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim" wrote in message
news:cHH_e.314911$on1.250098@clgrps13...
I need to know if this can be done. On sheet 2 I have 3 columns (A,B,C);
date from, date to and interest rate. I need a formula that would allow
me
on sheet 1 to input a date in one cell (D8) and have the corresponding
interest rate (as shown on sheet 2) appear in another (F8). I've been
messin with this for weeks and am at my wits end :)

Thanks, Jim

Bad logic. Try


=SUMPRODUCT(--(Sheet2!A2:A200<=D8),--(Sheet2!B2:B200=D8),Sheet2!C2:C200)


In article <1qV_e.283104$tt5.215852@edtnps90,
"Jim" wrote:

Didn't work, I tried

=SUMPRODUCT(--(Sheet2!A2:A200=D8),--(Sheet2!B2:B200<=D8),(Sheet2!C2:C200)

didn't work either :(

Thanks, Jim

"Bob Phillips" wrote in message
...
As long as the dates don't overlap you could use

=SUMPRODUCT(--(Sheet2!A2:A200=D8),--(Sheet2!B2:B200<=D8),C2:C200)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim" wrote in message
news:cHH_e.314911$on1.250098@clgrps13...
I need to know if this can be done. On sheet 2 I have 3 columns (A,B,C);
date from, date to and interest rate. I need a formula that would allow
me
on sheet 1 to input a date in one cell (D8) and have the corresponding
interest rate (as shown on sheet 2) appear in another (F8). I've been
messin with this for weeks and am at my wits end :)

Thanks, Jim

Neither works, getting #REF errors, I tried changing it slighly to match the
cells I actually use in the columns to no avail.. Just to make sure as I
have limited experience with excel, I am entering this formula into F8 cell
on sheet1 (Where I want the results to show)

=SUMPRODUCT(--(Sheet2!A3:A36=D8),--(Sheet2!B3:B36<=D8),C3:C36)


Jim

"JE McGimpsey" wrote in message
...
Bad logic. Try


=SUMPRODUCT(--(Sheet2!A2:A200<=D8),--(Sheet2!B2:B200=D8),Sheet2!C2:C200)


In article <1qV_e.283104$tt5.215852@edtnps90,
"Jim" wrote:

Didn't work, I tried


=SUMPRODUCT(--(Sheet2!A2:A200=D8),--(Sheet2!B2:B200<=D8),(Sheet2!C2:C200)

didn't work either :(

Thanks, Jim

"Bob Phillips" wrote in message
...
As long as the dates don't overlap you could use

=SUMPRODUCT(--(Sheet2!A2:A200=D8),--(Sheet2!B2:B200<=D8),C2:C200)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim" wrote in message
news:cHH_e.314911$on1.250098@clgrps13...
I need to know if this can be done. On sheet 2 I have 3 columns
(A,B,C);
date from, date to and interest rate. I need a formula that would
allow
me
on sheet 1 to input a date in one cell (D8) and have the
corresponding
interest rate (as shown on sheet 2) appear in another (F8). I've
been
messin with this for weeks and am at my wits end :)

Thanks, Jim

The formula I gave you works fine in a test workbook. Again, the
comparisons you're making are inverted logic - the comparisons should be

<start date range <=D8

and

<end date range =D8

The #REF! error would indicate that your references were bad - are you
sure that your sheet is named "Sheet2"?


In article <_GY_e.283159$tt5.57408@edtnps90,
"Jim" wrote:

Neither works, getting #REF errors, I tried changing it slighly to match the
cells I actually use in the columns to no avail.. Just to make sure as I
have limited experience with excel, I am entering this formula into F8 cell
on sheet1 (Where I want the results to show)

=SUMPRODUCT(--(Sheet2!A3:A36=D8),--(Sheet2!B3:B36<=D8),C3:C36)

That was embarrassing, lol.
I'm getting a response altho it's not returning the same interest rate as on
"Sheet2". On the first date range it's showing 89.75% when it should be 9%.
Somethings getting lost in the translation, however I'm closer than I've
been todate and thats a big step forward.

Thanks for your help, Jim

"JE McGimpsey" wrote in message
...
The formula I gave you works fine in a test workbook. Again, the
comparisons you're making are inverted logic - the comparisons should be

<start date range <=D8

and

<end date range =D8

The #REF! error would indicate that your references were bad - are you
sure that your sheet is named "Sheet2"?


In article <_GY_e.283159$tt5.57408@edtnps90,
"Jim" wrote:

Neither works, getting #REF errors, I tried changing it slighly to match
the
cells I actually use in the columns to no avail.. Just to make sure as I
have limited experience with excel, I am entering this formula into F8
cell
on sheet1 (Where I want the results to show)

=SUMPRODUCT(--(Sheet2!A3:A36=D8),--(Sheet2!B3:B36<=D8),C3:C36)

Are you sure that there is only one rate that matches the date in D8. If
there are many, this formula will sum them.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim" wrote in message
news:pqd%e.319610$on1.77599@clgrps13...
That was embarrassing, lol.
I'm getting a response altho it's not returning the same interest rate as
on
"Sheet2". On the first date range it's showing 89.75% when it should be
9%.
Somethings getting lost in the translation, however I'm closer than I've
been todate and thats a big step forward.

Thanks for your help, Jim

"JE McGimpsey" wrote in message
...
The formula I gave you works fine in a test workbook. Again, the
comparisons you're making are inverted logic - the comparisons should be

<start date range <=D8

and

<end date range =D8

The #REF! error would indicate that your references were bad - are you
sure that your sheet is named "Sheet2"?


In article <_GY_e.283159$tt5.57408@edtnps90,
"Jim" wrote:

Neither works, getting #REF errors, I tried changing it slighly to
match
the
cells I actually use in the columns to no avail.. Just to make sure as
I
have limited experience with excel, I am entering this formula into F8
cell
on sheet1 (Where I want the results to show)

=SUMPRODUCT(--(Sheet2!A3:A36=D8),--(Sheet2!B3:B36<=D8),C3:C36)