SUMPRODUCT statistics

umpexec

New Member
Joined
Mar 9, 2009
Messages
5
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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?
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top