Sum Week To Date Sales

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
251
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?

111111122222223333333
ProductSundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
A88705778298744785387916849971510482065786
B3111177944483698100323434878496163102372
C1007955963756907458847670127265605859868836
D298921281518502024111444331323365355548
E64625419230165145454110965225935767633
Product:C
Week:2
Day:Thursday
Result:362

<tbody>
</tbody>

Thanks
 

Some videos you may like

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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Jan 18, 2017
Messages
251
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
Joined
Sep 19, 2005
Messages
5,117
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:

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top