Auto Sum an Additional Row Weekly

[gweno]

I have a set of sales forecast data. Column A would be the weeks (1-52), column B would be the forecast dollars for Orange sales, column C for Peach sales, Column D for Apple sales and column E for Pears. After 13 weeks, the spreadsheet sums the weeks by quarter total dollars so that row might need to be eliminated (ie cell B15 would be a sum of wk 1-13, B16 would be wk 14 sales, after the 2nd qtr another row sums up the 2nd qtr sales,etc). I have a Counter cell which I update which week we are in. So if I'm in week 18, I have a cell that needs the results of Orange, Peach, Apple and Pear forecast sales on a quarterly basis (wks 14-18 in that example) and another cell is looking for the results for the year to date (wks 1-18). I made up that example, but there will be situations in which I might need the result to be just the sum of Peaches (column C) and sometimes I might need to add 2 or more columns for the result (sum of peaches and pears on qtrly and ytd basis for example).

I have the same data below times multiple locations. I think I might need VB code.

I appreciate any help you can give me and I hope this makes sense!
​

 

[AlphaFrog]

Here's an example of how you might do it. The numbers I used in the data table are made up and don't match your example data.

<br /><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>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Weeks</td><td style="font-weight: bold;text-align: center;;">Orange</td><td style="font-weight: bold;text-align: center;;">Peaches</td><td style="font-weight: bold;text-align: center;;">Appels</td><td style="font-weight: bold;text-align: center;;">Pears</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td><td style="text-align: center;;">22</td><td style="text-align: center;;">33</td><td style="text-align: center;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">11</td><td style="text-align: center;;">23</td><td style="text-align: center;;">34</td><td style="text-align: center;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">12</td><td style="text-align: center;;">24</td><td style="text-align: center;;">35</td><td style="text-align: center;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">13</td><td style="text-align: center;;">25</td><td style="text-align: center;;">36</td><td style="text-align: center;;">18</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">5</td><td style="text-align: center;;">14</td><td style="text-align: center;;">26</td><td style="text-align: center;;">37</td><td style="text-align: center;;">19</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">15</td><td style="text-align: center;;">27</td><td style="text-align: center;;">38</td><td style="text-align: center;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">7</td><td style="text-align: center;;">16</td><td style="text-align: center;;">28</td><td style="text-align: center;;">39</td><td style="text-align: center;;">21</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">8</td><td style="text-align: center;;">17</td><td style="text-align: center;;">29</td><td style="text-align: center;;">40</td><td style="text-align: center;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">9</td><td style="text-align: center;;">18</td><td style="text-align: center;;">30</td><td style="text-align: center;;">41</td><td style="text-align: center;;">23</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">10</td><td style="text-align: center;;">19</td><td style="text-align: center;;">31</td><td style="text-align: center;;">42</td><td style="text-align: center;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">11</td><td style="text-align: center;;">20</td><td style="text-align: center;;">32</td><td style="text-align: center;;">43</td><td style="text-align: center;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">12</td><td style="text-align: center;;">21</td><td style="text-align: center;;">33</td><td style="text-align: center;;">44</td><td style="text-align: center;;">26</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">13</td><td style="text-align: center;;">22</td><td style="text-align: center;;">34</td><td style="text-align: center;;">45</td><td style="text-align: center;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="font-weight: bold;text-align: center;;">Total</td><td style="font-weight: bold;text-align: center;;">208</td><td style="font-weight: bold;text-align: center;;">364</td><td style="font-weight: bold;text-align: center;;">507</td><td style="font-weight: bold;text-align: center;;">273</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">14</td><td style="text-align: center;;">24</td><td style="text-align: center;;">36</td><td style="text-align: center;;">47</td><td style="text-align: center;;">29</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">15</td><td style="text-align: center;;">25</td><td style="text-align: center;;">37</td><td style="text-align: center;;">48</td><td style="text-align: center;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">16</td><td style="text-align: center;;">26</td><td style="text-align: center;;">38</td><td style="text-align: center;;">49</td><td style="text-align: center;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">17</td><td style="text-align: center;;">27</td><td style="text-align: center;;">39</td><td style="text-align: center;;">50</td><td style="text-align: center;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">18</td><td style="text-align: center;;">28</td><td style="text-align: center;;">40</td><td style="text-align: center;;">51</td><td style="text-align: center;;">33</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">19</td><td style="text-align: center;;">29</td><td style="text-align: center;;">41</td><td style="text-align: center;;">52</td><td style="text-align: center;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">20</td><td style="text-align: center;;">30</td><td style="text-align: center;;">42</td><td style="text-align: center;;">53</td><td style="text-align: center;;">35</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">21</td><td style="text-align: center;;">31</td><td style="text-align: center;;">43</td><td style="text-align: center;;">54</td><td style="text-align: center;;">36</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">22</td><td style="text-align: center;;">32</td><td style="text-align: center;;">44</td><td style="text-align: center;;">55</td><td style="text-align: center;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">23</td><td style="text-align: center;;">33</td><td style="text-align: center;;">45</td><td style="text-align: center;;">56</td><td style="text-align: center;;">38</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">24</td><td style="text-align: center;;">34</td><td style="text-align: center;;">46</td><td style="text-align: center;;">57</td><td style="text-align: center;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">25</td><td style="text-align: center;;">35</td><td style="text-align: center;;">47</td><td style="text-align: center;;">58</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">26</td><td style="text-align: center;;">36</td><td style="text-align: center;;">48</td><td style="text-align: center;;">59</td><td style="text-align: center;;">41</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="font-weight: bold;text-align: center;;">Total</td><td style="font-weight: bold;text-align: center;;">598</td><td style="font-weight: bold;text-align: center;;">910</td><td style="font-weight: bold;text-align: center;;">1196</td><td style="font-weight: bold;text-align: center;;">728</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">27</td><td style="text-align: center;;">38</td><td style="text-align: center;;">50</td><td style="text-align: center;;">61</td><td style="text-align: center;;">43</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">28</td><td style="text-align: center;;">39</td><td style="text-align: center;;">51</td><td style="text-align: center;;">62</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">29</td><td style="text-align: center;;">40</td><td style="text-align: center;;">52</td><td style="text-align: center;;">63</td><td style="text-align: center;;">45</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;;">30</td><td style="text-align: center;;">41</td><td style="text-align: center;;">53</td><td style="text-align: center;;">64</td><td style="text-align: center;;">46</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;;">31</td><td style="text-align: center;;">42</td><td style="text-align: center;;">54</td><td style="text-align: center;;">65</td><td style="text-align: center;;">47</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: center;;">32</td><td style="text-align: center;;">43</td><td style="text-align: center;;">55</td><td style="text-align: center;;">66</td><td style="text-align: center;;">48</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: center;;">33</td><td style="text-align: center;;">44</td><td style="text-align: center;;">56</td><td style="text-align: center;;">67</td><td style="text-align: center;;">49</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;;">34</td><td style="text-align: center;;">45</td><td style="text-align: center;;">57</td><td style="text-align: center;;">68</td><td style="text-align: center;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;;">35</td><td style="text-align: center;;">46</td><td style="text-align: center;;">58</td><td style="text-align: center;;">69</td><td style="text-align: center;;">51</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: center;;">36</td><td style="text-align: center;;">47</td><td style="text-align: center;;">59</td><td style="text-align: center;;">70</td><td style="text-align: center;;">52</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;;">37</td><td style="text-align: center;;">48</td><td style="text-align: center;;">60</td><td style="text-align: center;;">71</td><td style="text-align: center;;">53</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: center;;">38</td><td style="text-align: center;;">49</td><td style="text-align: center;;">61</td><td style="text-align: center;;">72</td><td style="text-align: center;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;;">39</td><td style="text-align: center;;">50</td><td style="text-align: center;;">62</td><td style="text-align: center;;">73</td><td style="text-align: center;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="font-weight: bold;text-align: center;;">Total</td><td style="font-weight: bold;text-align: center;;">1170</td><td style="font-weight: bold;text-align: center;;">1638</td><td style="font-weight: bold;text-align: center;;">2067</td><td style="font-weight: bold;text-align: center;;">1365</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: center;;">40</td><td style="text-align: center;;">52</td><td style="text-align: center;;">64</td><td style="text-align: center;;">75</td><td style="text-align: center;;">57</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: center;;">41</td><td style="text-align: center;;">53</td><td style="text-align: center;;">65</td><td style="text-align: center;;">76</td><td style="text-align: center;;">58</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: center;;">42</td><td style="text-align: center;;">54</td><td style="text-align: center;;">66</td><td style="text-align: center;;">77</td><td style="text-align: center;;">59</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: center;;">43</td><td style="text-align: center;;">55</td><td style="text-align: center;;">67</td><td style="text-align: center;;">78</td><td style="text-align: center;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: center;;">44</td><td style="text-align: center;;">56</td><td style="text-align: center;;">68</td><td style="text-align: center;;">79</td><td style="text-align: center;;">61</td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="text-align: center;;">45</td><td style="text-align: center;;">57</td><td style="text-align: center;;">69</td><td style="text-align: center;;">80</td><td style="text-align: center;;">62</td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: center;;">46</td><td style="text-align: center;;">58</td><td style="text-align: center;;">70</td><td style="text-align: center;;">81</td><td style="text-align: center;;">63</td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: center;;">47</td><td style="text-align: center;;">59</td><td style="text-align: center;;">71</td><td style="text-align: center;;">82</td><td style="text-align: center;;">64</td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: center;;">48</td><td style="text-align: center;;">60</td><td style="text-align: center;;">72</td><td style="text-align: center;;">83</td><td style="text-align: center;;">65</td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style="text-align: center;;">49</td><td style="text-align: center;;">61</td><td style="text-align: center;;">73</td><td style="text-align: center;;">84</td><td style="text-align: center;;">66</td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style="text-align: center;;">50</td><td style="text-align: center;;">62</td><td style="text-align: center;;">74</td><td style="text-align: center;;">85</td><td style="text-align: center;;">67</td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style="text-align: center;;">51</td><td style="text-align: center;;">63</td><td style="text-align: center;;">75</td><td style="text-align: center;;">86</td><td style="text-align: center;;">68</td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style="text-align: center;;">52</td><td style="text-align: center;;">64</td><td style="text-align: center;;">76</td><td style="text-align: center;;">87</td><td style="text-align: center;;">69</td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style="font-weight: bold;text-align: center;;">Total</td><td style="font-weight: bold;text-align: center;;">1924</td><td style="font-weight: bold;text-align: center;;">2548</td><td style="font-weight: bold;text-align: center;;">3120</td><td style="font-weight: bold;text-align: center;;">2184</td></tr></tbody></table><br /><br />

<br /><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>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Current week</td><td style="font-weight: bold;text-align: center;;">Orange YTD</td><td style="font-weight: bold;text-align: center;;">Peaches YTD</td><td style="font-weight: bold;text-align: center;;">Apples YTD</td><td style="font-weight: bold;text-align: center;;">Pears YTD</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">18</td><td style="text-align: center;;">338</td><td style="text-align: center;;">554</td><td style="text-align: center;;">752</td><td style="text-align: center;;">428</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: center;;">x</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;;">Selected items YTD</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1090</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><br /><br /><table 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">G2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$2</font>),B$2:B$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$2</font>),C$2:C$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$2</font>),D$2:D$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$2</font>),E$2:E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=SUMIF(<font color="Blue">G3:J3,"x",G2:J2</font>)</td></tr></tbody></table></td></tr></table><br />

 

[gweno]

Thank u SO SO SO much!!

is there a way to do quarter to date numbers that don't need to be edited every quarter and are good for the year?

Week 18 QTD would be just weeks 14-18 for example.

 

[AlphaFrog]

<br /><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>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;;">Current week</td><td style="font-weight: bold;text-align: center;;">Orange QTD</td><td style="font-weight: bold;text-align: center;;">Peaches QTD</td><td style="font-weight: bold;text-align: center;;">Apples QTD</td><td style="font-weight: bold;text-align: center;;">Pears QTD</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">18</td><td style="text-align: center;;">130</td><td style="text-align: center;;">346</td><td style="text-align: center;;">544</td><td style="text-align: center;;">220</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: center;;">x</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;;">Selected items QTD</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="text-align: center;;">674</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></tbody></table><br /><br /><table 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">G8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),B$2:B$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,$B$15,$B$29,$B$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),C$2:C$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,$B$15,$B$29,$B$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),D$2:D$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,$B$15,$B$29,$B$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),E$2:E$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,$B$15,$B$29,$B$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F11</th><td style="text-align:left">=SUMIF(<font color="Blue">G9:J9,"x",G8:J8</font>)</td></tr></tbody></table></td></tr></table><br />

 

[gweno]

once i get to wk 27, it stops working... is that happening for you as well?

once again, thanks so much!! Its almost there and i love it!

 

[AlphaFrog]

Try this...

<br /><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>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-left: 1px solid black;;">Current week</td><td style="font-weight: bold;text-align: center;;">Orange QTD</td><td style="font-weight: bold;text-align: center;;">Peaches QTD</td><td style="font-weight: bold;text-align: center;;">Apples QTD</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;">Pears QTD</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-left: 1px solid black;;">27</td><td style="text-align: center;;">38</td><td style="text-align: center;;">50</td><td style="text-align: center;;">61</td><td style="text-align: center;;">43</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: center;;">x</td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;border-left: 1px solid black;;">Selected items QTD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;">99</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr></tbody></table><br /><br /><table 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">G8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),B$2:B$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,B$15,B$29,B$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),C$2:C$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,C$15,C$29,C$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),D$2:D$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,D$15,D$29,D$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$56<>"Total"</font>),--(<font color="Red">$A$2:$A$56<=$F$8</font>),E$2:E$56</font>)-CHOOSE(<font color="Blue">INT(<font color="Red">(<font color="Green">$F$8-1</font>)/13</font>)+1,0,E$15,E$29,E$43</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F11</th><td style="text-align:left">=SUMIF(<font color="Blue">G9:J9,"x",G8:J8</font>)</td></tr></tbody></table></td></tr></table><br />

Changed this...
CHOOSE(INT(($F$8-1)/13)+1,0,$B$15,$B$29,$B$43)
To this...
CHOOSE(INT(($F$8-1)/13)+1,0,B$15,B$29,B$43)

 

[gweno]

i wasn't using the $$ signs so let me try that....

 

[gweno]

added a bunch of $$ signs and same results... It works until wk 26 and nothing after that.

 

[AlphaFrog]

It works for me using the data table from post #2

Double check your formulas. Are you using the same configuration as I am? What doesn't work? Can you tell me if it gives the wrong answer or no answer or what?

 

[gweno]

i think I figured out what it does...

when I do wk 27, the result should be just that one week, and I get the result of wks 1-13, plus wk 27.

wk 28 gives me wk 1-13 plus 27 and 28, rather than just 27 and 28.

ok jump ahead to wk 45 QTD which should be wk40-45, and I get wk 1-13, wk 14-26, and wk 40-45

wk 37 seems to add Qtr 2 (14-26) and wk27-37