SUMPRODUCT statistics

umpexec

New Member
Hi All,

On Sheet1 I have 2 cells one has the date 1-mar-09 and the other has 7-mar-09. I need to find this range on Sheet2 which has all the dates for the year in ColumnA and then total the amounts found in Sheet2!ColumnB for the specified date range. Is this possible without VB?

Thanks,
Leanne

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Mr. Walnuts

Board Regular
are you looking to result the cooresponding amount for that date in each cell or the SUM of the amounts from date A to date B?

Mr. Walnuts

Board Regular
if I understand this correctly, you would like the TOTAL amount FROM 1 Mar 09 to 7 Mar 09 based on the cooresponding amounts next to the dates on sheet 2, correct?

If this is the case, it is very possible.

Post the cells from sheet 1... and also the Date range from Sheet 2 and the amount range from sheet 2.

Also post the cells from sheet 1 you want the totals in

umpexec

New Member
Hi Mr.Walnuts,

The problem is that the date range on sheet1 will change regulary and I didnt want to constantly update sheet1 with new data or formulas. I would much rather change the date on Sheet1 and it grabs the data from sheet2 automatically.

Thanks
L

Last edited:

umpexec

New Member
Sheet1 Data
<TABLE style="WIDTH: 176pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=234 border=0><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD id=td_post_394990 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=79 height=21>WEEK OF:</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=78>1-Mar-09</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 58pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=77>7-Mar-09</TD></TR></TBODY></TABLE>

Sheet2 data<TABLE style="WIDTH: 274pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=364 border=0><COLGROUP><COL style="WIDTH: 160pt; mso-width-source: userset; mso-width-alt: 7789" width=213><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 160pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=213 height=20>1-Mar-09</TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=78>12,298</TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 55pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=73>71,391</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2-Mar-09</TD><TD class=xl66 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=78>12,057</TD><TD class=xl66 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 55pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=73>77,697</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3-Mar-09</TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=78>11,390</TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 55pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=73>75,521</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>4-Mar-09 </TD><TD class=xl66 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=78>10,664</TD><TD class=xl66 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 55pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=73>68,852</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5-Mar-09 </TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=78>10,752</TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 55pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=73>69,059</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6-Mar-09</TD><TD class=xl66 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=78>10,565</TD><TD class=xl66 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 55pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=73>64,904</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7-Mar-09</TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=78>9,903</TD><TD class=xl65 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 55pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #eeeeee" width=73>60,197</TD></TR></TBODY></TABLE>

Sheet1 where the value should return
<TABLE style="WIDTH: 118pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=157 border=0><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 id=td_post_394990 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c5d9f1" width=79 height=20>Prior Week</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=78>118,329</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c5d9f1" height=20>Last Week</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>103,042</TD></TR></TBODY></TABLE>

Last edited:

Mr. Walnuts

Board Regular
Regardless of whether the date on sheet 1 changes, the formula will INDEX that date with the dates on sheet 2 and return the cooresponding amounts. If it needs to return the TOTAL from one date on sheet 2 to another date on sheet 2, thats fine also.

Mr. Walnuts

Board Regular
What cells are the dates in on sheet 1?

do you need the cooresponding amounts for those dates... or the sum from one date to the other?

umpexec

New Member
Sheet1 dates are D5 and E5
Sheet2 the dates are A7:A375 and the values are B7:B375

The value should appear on Sheet 1 in cell B12

I need the sum of one date to the other.

Thanks Walnuts!
L

MrExcel MVP
Sheet1 dates are D5 and E5
Sheet2 the dates are A7:A375 and the values are B7:B375

The value should appear on Sheet 1 in cell B12

I need the sum of one date to the other.

Thanks Walnuts!
L

One option is to use Data|PivotTable.

Otherwise:

=SUMPRODUCT(--(\$A\$7:\$A\$375>=D5),--(\$A\$7:\$A\$375<=E5),\$B\$7:\$B\$375)

umpexec

New Member
One option is to use Data|PivotTable.

Otherwise:

=SUMPRODUCT(--(\$A\$7:\$A\$375>=D5),--(\$A\$7:\$A\$375<=E5),\$B\$7:\$B\$375)

It works! Thanks for the help. What are the dashes for?
Leanne

Replies
3
Views
170
Replies
2
Views
231
Replies
8
Views
375
Replies
6
Views
224
Replies
4
Views
1K

1,190,655
Messages
5,982,131
Members
439,757
Latest member
85Sarah2005

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.

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