Determine value if a date is with two date

Hello, I need to determine the Annual Salary at the time a bonus was given. There's a bonus date and a sal begin and end date.  If the bonus date falls between the sal begin and end date then I need the Annual Date reflected in Column E for each EEID.  Please see attached example.  

There are two sets of data
1) EEID, Date of Bonus and bonus amount
2) EEID, Sal Beg Date, Sal End Date and Annual Salary Amount
compensation2.xlsx
ablove3Asked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
Use this formula in cell E2 and copy down...

=SUMPRODUCT((H1:H178=A2)*(I1:I178<=B2)*(J1:J178>=B2),K1:K178)
0
 
Don VonderBurgPresident/CEOCommented:
Use the AND function with IF

=IF(AND(B2>=I2,B2<=J2),K2,0)

Open in new window

0
 
Rob HensonFinance AnalystCommented:
I believe you can use this formula:

=SUMIFS($K:$K,$H:$H,$A2,$I:$I,"<="&$B2,$J:$J,">="&$B2)

However, that gives a different result for the first entry (as does Wayne's suggestion) but I believe our results are correct as the $210,000 does not correspond with the dates.

EEID 1 was earning $194,828 between 03/03/07 and 29/02/08 and the bonus was on 29/02/08 so should have result of $194,828

Thanks
Rob
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ablove3Author Commented:
Thank you so much for resolving this so quickly. Both of Wayne Taylor and Rob Hensons' solutions worked perfectly.  Rob you are correct $210K was an oversight on my part.
0
 
ablove3Author Commented:
Thank you both so much.  Sorry for taking so long to close this ticket.
0
 
Rob HensonFinance AnalystCommented:
You have marked your own comment as Assisted.

Can I ask that you Request Attention to reassign points?

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.