using dates to defien what to sum

[Smithgall]

i have four collumns and Xnumber of rows. A1 is the name,B1 is the start date, C1 is the end date and D2 is a sum. A2 through Ax is a series of dropdown boxes with employee names. What i want to do is select a name from a dropdown. put in the start date and then the end date and have collumn D sum all the wdigets they produced between that time.

The widgets are entered on another sheet in the same workbook. that sheet is simply A1:A365 with the date on each row and then column B holding the actual number of widgets produced on that day.

What i am having problems with is saying look for the dates in this collumn and then sum all the values from a corresponding column and rows.

 

[Brian from Maui]

i have four collumns and Xnumber of rows. A1 is the name,B1 is the start date, C1 is the end date and D2 is a sum. A2 through Ax is a series of dropdown boxes with employee names. What i want to do is select a name from a dropdown. put in the start date and then the end date and have collumn D sum all the wdigets they produced between that time.

The widgets are entered on another sheet in the same workbook. that sheet is simply A1:A365 with the date on each row and then column B holding the actual number of widgets produced on that day.

What i am having problems with is saying look for the dates in this collumn and then sum all the values from a corresponding column and rows.

A couple of options,

=SUMIF(A1:A365,">="&Sheet1!B1,B1:B365)-SUMIF(A1:A365,">"&Sheet!C1,B1:B365)

=SUMPRODUCT(-(A1:A365>=Sheet1!B1),--(A1:A365 < Sheet1!C1),B1:B365)

Where Sheet1 is the worksheet housing the dates and column to sum.

Edited for missing paren

 

[NBVC]

Try:

=SUMPRODUCT(--(SHEET2!$A$1:$A$365>=B2),--(SHEET2!$A$1:$A$365<=C2),SHEET2!$B$1:$B$365)

Does the person's name come into play at all, though?

 

[Smithgall]

Yes he name does come intp play.

I should have mentioned that. The drop down will determine what sheet is looked at for the date( if they choose Mike then then sheet Mike is used for the data). So the code would remain the same except i will have to add some sort of if statement to choose the right sheet depending on what name was chosen from the drop down box.

i havent tried the code yet but thanks in advance.

 

[Brian from Maui]

Re: Yes he name does come intp play.

I should have mentioned that. The drop down will determine what sheet is looked at for the date( if they choose Mike then then sheet Mike is used for the data). So the code would remain the same except i will have to add some sort of if statement to choose the right sheet depending on what name was chosen from the drop down box.

i havent tried the code yet but thanks in advance.

I hope you don't have too many of these formulae,

=SUMPRODUCT(--(INDIRECT("'"&A1&"'!A1:A365")>=B1),--(INDIRECT("'"&A1&"'!A1:A365")<=C1),INDIRECT("'"&A1&"'!B1:B365"))