Lookup Formula

[if_Excel]

I HAVE some data generated by a database for the date an employee worked and how much work they did. the query returns 7 days worth of data and i could have multiple entries for a single date. i need to know how much apple is picked from the beggiing of the week to the current date. the week begins on Sunday. So if on a wednesday i want to find how many has been picked to date, i want to search the returned data (7 days) using the begining of the week date, the returned date and current date as the criteria. the data looks like this
col A (retdate) col B (Ret cnt) Col C (wK begin) Col D (today) colE(wksum
7/1/11 500 6/26/11 7/1/11
6/30/11 200
6/30/11 320
6/29/11 400
6/28/11 180
6/28/11 200
6/27/11 100
6/26/11 550
6/25/11 410
i think the formula will search the array in A using C and D as the criteria and return the sum of B mathcing the criteria in E. I think it might be a vlookup or sumif. i just cant get the construction right.
thanks

 

[Aladin Akyurek]

It's unclear what you have and what you require. Try to repost the sample along with the expected results.

 

[if_Excel]

sorry for the confusion. what i want is to search dates and quantity returned from a database by matching dates from the beginnig of current week till the current day and return the sum of the quantity for all dates that matches.

 

[Aladin Akyurek]

sorry for the confusion. what i want is to search dates and quantity returned from a database by matching dates from the beginnig of current week till the current day and return the sum of the quantity for all dates that matches.

Should not be that diffcult to post a very tiny sample along with the desired result... Guessing:

Control+shift+enter, not just enter...

=SUM(IF(DateRange>=StartDate,IF(DateRange<=TODAY(),QuantityRange)))

If you are on Excel 2007 or later, just enter...

=SUMIFS(QuantityRange,DateRange,">="&StartDate,DateRange,"<="&TODAY())

 

[if_Excel]

thanks a lot Mr Aladin. it works.

 

[Aladin Akyurek]

thanks a lot Mr Aladin. it works.

Glad to hear that. Thanks for providing feedback.