# using dates to defien what to sum

#### Smithgall

##### Board Regular
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

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?

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.

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"))

Replies
3
Views
164
Replies
18
Views
421
Replies
6
Views
262
Replies
1
Views
203
Replies
7
Views
116

1,217,319
Messages
6,135,865
Members
449,965
Latest member
Ckl43

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back