# Lookup Date 14 days prior and add multiple values for that date into column

1. ## Lookup Date 14 days prior and add multiple values for that date into column

Hi,

I hope someone can help me with this problem because I can't even figure out where to start.

I have a workbook with 2 sheets. A wages sheet and a commissions sheet. The commissions are withheld for 2 weeks so what I am trying to do is put together a Gross Salary + Commissions for that pay period.

So if the pay period (for wages only) is 5/19/2013 to 6/1/13 I need to add in the commissions that were earned during the previous pay period (or date range) which was 5/5/13 to 5/18/13.

For each day during that period (5/5/13 to 5/18/13), there are multiple commissions earned.

I am trying to create a formula that will for example, on the wages sheet, look at the date on the 19th, find or search through the dates on the commissions sheet until it finds a date 14 days prior and "forall" occurrences of commissions earned on that date(which would be the 5th) add them together and pull them in to the Gross pay column on the wages sheet, also adding in the wages earned on the 19th.

So gross pay would equal wages earned on the 19th+all commissions earned on the 5th.

I hope this is making sense, because I'm confusing myself!!

I have attached the workbook that I am utilizing in the hopes that someone will understand what I am trying to do here. Thanks!

Pay and Barrel Commission.xlsx

2. ## Re: Lookup Date 14 days prior and add multiple values for that date into column

I'm not 100% following because you changed your logic somewhere along the way.. but if I understand your situation, you are trying to add the wages in cell D2 to the commissions earned from 5/5 thru 5/18, and not just the commissions earned on 5/5. If that is what you want, you could try this formula:
Formula:
`Please Login or Register  to view this content.`

If you do just really want the wages in D2 added to the commissions from 5/5 (14 days prior) then use this formula:
Formula:
`Please Login or Register  to view this content.`

Also... figured I should mention this... ALL of the cells in column A of the 'Barrel Commissions' sheet need to have corresponding dates in them.

- Moo

3. ## Re: Lookup Date 14 days prior and add multiple values for that date into column

If you do want to avoid inputting a date in every cell in column a then insert a Hidden Column B with the following formula and reference this column for date look-ups.
Formula:
`Please Login or Register  to view this content.`

Just remember that you will have to fill this column down as your excel sheet grows. Or you can create this column, Copy and Paste it as Values, and then delete Column A and begin putting a date to every cell in the future.

4. ## Re: Lookup Date 14 days prior and add multiple values for that date into column

not sure if this is exactly what you are looking for, but see attachment
I added a helper column in 'Barrel Commissions', F2:
Formula:
`Please Login or Register  to view this content.`

Drag down

then in 'Hourly Wages',F2 :
Formula:
`Please Login or Register  to view this content.`

Drag down

Hope this helps

EDIT-
forgot attachment..sorry

5. ## Re: Lookup Date 14 days prior and add multiple values for that date into column

That is EXACTLY what I was looking for! Thank you so much! I have been racking my brains for 2 days trying to figure out how to do this, especially without having to fill in the date for every single commission for the same day! That's PERFECT!! Thanks a Million Dredwolf!!

6. ## Re: Lookup Date 14 days prior and add multiple values for that date into column

Thank you Moo, Spitzerpl and DredWolf!! I really appreciate the help on this, I don't think I would have figured this out on my own. Thank you!!

7. ## Re: Lookup Date 14 days prior and add multiple values for that date into column

You are quite welcome, that's why we are here

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1