Sum Week To Date Sales

Lewzerrrr

Active Member
Hi

I have a 26 week daily sales data dump that I need to sum X week week to date

I have my week number (1, 2.. 26) along row 1, split out by Sunday, Monday... Saturday in row 2 with sales data below

Below is required output, how can I go about this?

 1 1 1 1 1 1 1 2 2 2 2 2 2 2 3 3 3 3 3 3 3 Product Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday A 88 70 57 78 29 87 44 78 53 87 91 68 49 97 15 10 48 20 65 7 86 B 31 11 17 79 44 48 36 98 100 3 23 43 4 8 78 49 61 63 10 23 72 C 100 79 55 96 37 56 90 74 58 84 76 70 12 72 65 60 58 59 86 88 36 D 2 98 92 12 81 5 18 50 20 24 11 14 44 33 13 23 36 5 35 55 48 E 64 6 2 54 19 2 30 16 51 45 45 41 10 96 5 22 59 35 76 76 33 Product: C Week: 2 Day: Thursday Result: 362

<tbody>
</tbody>

Thanks

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

XOR LX

Well-known Member
Hi,

Assuming your data table, Product, Week and Day are in A1:V8, B9, B10 and B11 respetively:

=SUMPRODUCT(SUMIFS(INDEX(A:XFD,MATCH(B9,A:A,0),0),1:1,B10,2:2,CHOOSE(MATCH(LEFT(B11,2),{"F","M","S","Su","T","Tu","W"}),"<>Sa",{"Su","M"},"*","Su",{"Su","M","T","W"},{"Su","M","Tu"},{"Su","M","Tu","W"})&"*"))

Regards

Last edited:

Lewzerrrr

Active Member
Hi,

Assuming your data table, Product, Week and Day are in A1:V8, B9, B10 and B11 respetively:

=SUMPRODUCT(SUMIFS(INDEX(A:XFD,MATCH(B9,A:A,0),0),1:1,B10,2:2,CHOOSE(MATCH(LEFT(B11,2),{"F","M","S","Su","T","Tu","W"}),"<>Sa",{"Su","M"},"*","Su",{"Su","M","T","W"},{"Su","M","Tu"},{"Su","M","Tu","W"})&"*"))

Regards

Thank you, this looks like the devils formula!

I used a helper column for now, inserted before column A. A3 = =SUM(INDEX(C3:W3,MATCH(\$C\$10,\$C\$1:\$W\$1,0)):INDEX(C3:W3,MATCH(1,(\$C\$10=\$C\$1:\$W\$1)*(\$C\$11=\$C\$2:\$W\$2),0))) (Ctrl Shift Enter) and C12 = =INDEX(A3:A7,MATCH(C9,B3:B7,0))

Thanks

sheetspread

Well-known Member
Try:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Product</td><td style=";">Sunday</td><td style=";">Monday</td><td style=";">Tuesday</td><td style=";">Wednesday</td><td style=";">Thursday</td><td style=";">Friday</td><td style=";">Saturday</td><td style=";">Sunday</td><td style=";">Monday</td><td style=";">Tuesday</td><td style=";">Wednesday</td><td style=";">Thursday</td><td style=";">Friday</td><td style=";">Saturday</td><td style=";">Sunday</td><td style=";">Monday</td><td style=";">Tuesday</td><td style=";">Wednesday</td><td style=";">Thursday</td><td style=";">Friday</td><td style=";">Saturday</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A</td><td style="text-align: right;;">88</td><td style="text-align: right;;">70</td><td style="text-align: right;;">57</td><td style="text-align: right;;">78</td><td style="text-align: right;;">29</td><td style="text-align: right;;">87</td><td style="text-align: right;;">44</td><td style="text-align: right;;">78</td><td style="text-align: right;;">53</td><td style="text-align: right;;">87</td><td style="text-align: right;;">91</td><td style="text-align: right;;">68</td><td style="text-align: right;;">49</td><td style="text-align: right;;">97</td><td style="text-align: right;;">15</td><td style="text-align: right;;">10</td><td style="text-align: right;;">48</td><td style="text-align: right;;">20</td><td style="text-align: right;;">65</td><td style="text-align: right;;">7</td><td style="text-align: right;;">86</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">B</td><td style="text-align: right;;">31</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">79</td><td style="text-align: right;;">44</td><td style="text-align: right;;">48</td><td style="text-align: right;;">36</td><td style="text-align: right;;">98</td><td style="text-align: right;;">100</td><td style="text-align: right;;">3</td><td style="text-align: right;;">23</td><td style="text-align: right;;">43</td><td style="text-align: right;;">4</td><td style="text-align: right;;">8</td><td style="text-align: right;;">78</td><td style="text-align: right;;">49</td><td style="text-align: right;;">61</td><td style="text-align: right;;">63</td><td style="text-align: right;;">10</td><td style="text-align: right;;">23</td><td style="text-align: right;;">72</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">C</td><td style="text-align: right;;">100</td><td style="text-align: right;;">79</td><td style="text-align: right;;">55</td><td style="text-align: right;;">96</td><td style="text-align: right;;">37</td><td style="text-align: right;;">56</td><td style="text-align: right;;">90</td><td style="text-align: right;;">74</td><td style="text-align: right;;">58</td><td style="text-align: right;;">84</td><td style="text-align: right;;">76</td><td style="text-align: right;;">70</td><td style="text-align: right;;">12</td><td style="text-align: right;;">72</td><td style="text-align: right;;">65</td><td style="text-align: right;;">60</td><td style="text-align: right;;">58</td><td style="text-align: right;;">59</td><td style="text-align: right;;">86</td><td style="text-align: right;;">88</td><td style="text-align: right;;">36</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">D</td><td style="text-align: right;;">2</td><td style="text-align: right;;">98</td><td style="text-align: right;;">92</td><td style="text-align: right;;">12</td><td style="text-align: right;;">81</td><td style="text-align: right;;">5</td><td style="text-align: right;;">18</td><td style="text-align: right;;">50</td><td style="text-align: right;;">20</td><td style="text-align: right;;">24</td><td style="text-align: right;;">11</td><td style="text-align: right;;">14</td><td style="text-align: right;;">44</td><td style="text-align: right;;">33</td><td style="text-align: right;;">13</td><td style="text-align: right;;">23</td><td style="text-align: right;;">36</td><td style="text-align: right;;">5</td><td style="text-align: right;;">35</td><td style="text-align: right;;">55</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">E</td><td style="text-align: right;;">64</td><td style="text-align: right;;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;">54</td><td style="text-align: right;;">19</td><td style="text-align: right;;">2</td><td style="text-align: right;;">30</td><td style="text-align: right;;">16</td><td style="text-align: right;;">51</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">41</td><td style="text-align: right;;">10</td><td style="text-align: right;;">96</td><td style="text-align: right;;">5</td><td style="text-align: right;;">22</td><td style="text-align: right;;">59</td><td style="text-align: right;;">35</td><td style="text-align: right;;">76</td><td style="text-align: right;;">76</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: rgb(22,17,32);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: 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><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><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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Product:</td><td style="text-align: right;;">C</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><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><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><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: rgb(22,17,32);text-align: center;">10</td><td style=";">Week:</td><td style="text-align: right;;">2</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><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><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><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: rgb(22,17,32);text-align: center;">11</td><td style=";">Day:</td><td style="text-align: right;;">Thursday</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><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><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><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: rgb(22,17,32);text-align: center;">12</td><td style=";">Result:</td><td style="text-align: right;;">362</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><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><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><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></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=SUM(<font color="Blue">OFFSET(<font color="Red">INDEX(<font color="Green">\$B\$3:\$V\$7,MATCH(<font color="Purple">B9,\$A\$3:\$A\$7,0</font>),MATCH(<font color="Purple">B10,\$B\$1:\$V\$1,0</font>)</font>),,,,MATCH(<font color="Green">B11,\$B\$2:\$H\$2,0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

or:

=SUMPRODUCT(((\$A\$3:\$A\$7=B9)*\$B\$1:\$V\$1=B10)*(MOD(COLUMN(B1:V1)-2,7)+1<=MATCH(B11,B2:H2,0))*\$B\$3:\$V\$7)

Weekday, weeknum, etc. works if the headers are actual dates.

Of course the solution is even easier when your data is in normal, unpivoted format.

Last edited:

Replies
7
Views
63
Replies
2
Views
46
Replies
9
Views
110
Replies
9
Views
141
Replies
20
Views
153

Threads
1,108,614
Messages
5,523,919
Members
409,542
Latest member
Shezz01