formula for date range

[proctk]

HI

I have this formula that sums all the tabs " The values in V6:V57 are 52 week dates. I want to some how change this formula so that it only sums upto a date that I would enter in say cell E3.

=SUMPRODUCT(SUMIF(INDIRECT("'"&V6:V57&"'!A:A"),G3,INDIRECT("'"&V6:V57&"'!i:i")))

Any help is great!

 

[barry houdini]

Can you give some sample sheet names, what format are those dates in?

 

[Aladin Akyurek]

HI

I have this formula that sums all the tabs " The values in V6:V57 are 52 week dates. I want to some how change this formula so that it only sums upto a date that I would enter in say cell E3.

=SUMPRODUCT(SUMIF(INDIRECT("'"&V6:V57&"'!A:A"),G3,INDIRECT("'"&V6:V57&"'!i:i")))

Any help is great!

Care to post the name of the sheet housing the formula you have?

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>E</th><th>F</th><th>G</th><th>V</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #FFFF00;;">SUM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">533</td><td style="text-align: center;;">All Week</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">235</td><td style="text-align: center;background-color: #FFFF00;;">Week2</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">H</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Week1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Week2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Week3</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Week4</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style=";">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Master</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&V6:V9&"'!A1:A20"</font>),G3,INDIRECT(<font color="Green">"'"&V6:V9&"'!I1:I20"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&V6:INDEX(<font color="Purple">V6:V9,MATCH(<font color="Teal">E3,V6:V9,0</font>)</font>)&"'!A1:A20"</font>),G3,INDIRECT(<font color="Green">"'"&V6:INDEX(<font color="Purple">V6:V9,MATCH(<font color="Teal">E3,V6:V9,0</font>)</font>)&"'!I1:I20"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">="Week"&RANDBETWEEN(<font color="Blue">1,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=MID(<font color="Blue">"ABCDEFGHIJ",RANDBETWEEN(<font color="Red">1,10</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[T. Valko]

HI

I have this formula that sums all the tabs " The values in V6:V57 are 52 week dates. I want to some how change this formula so that it only sums upto a date that I would enter in say cell E3.

=SUMPRODUCT(SUMIF(INDIRECT("'"&V6:V57&"'!A:A"),G3,INDIRECT("'"&V6:V57&"'!i:i")))

Any help is great!

Try something like this...

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(V6,,,MATCH(E3,V6:V57,0))&"'!A:A"),G3,INDIRECT("'"&OFFSET(V6,,,MATCH(E3,V6:V57,0))&"'!I:I")))

 

[proctk]

Perfect, thank you.

 

[T. Valko]

Perfect, thank you.

You're welcome. Thanks for the feedback!