Sum Week To Date Sales

L

Lewzerrrr

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

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

Thanks
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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:
Upvote 0
XOR LX said:
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
Click to expand...

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
 
Upvote 0
Try:


Excel 2010
ABCDEFGHIJKLMNOPQRSTUV
1111111122222223333333
2ProductSundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
3A88705778298744785387916849971510482065786
4B3111177944483698100323434878496163102372
5C1007955963756907458847670127265605859868836
6D298921281518502024111444331323365355548
7E64625419230165145454110965225935767633
8
9Product:C
10Week:2
11Day:Thursday
12Result:362
Sheet5
Cell Formulas
RangeFormula
B12=SUM(OFFSET(INDEX($B$3:$V$7,MATCH(B9,$A$3:$A$7,0),MATCH(B10,$B$1:$V$1,0)),,,,MATCH(B11,$B$2:$H$2,0)))


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:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
422
Eric W
Eric W
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
120
6StringJazzer
6StringJazzer
T
  • Solved
Automatically generate dates for specific days of the week on any given month
Replies
6
Views
294
Peter_SSs
Peter_SSs
C
  • Question Question
if Function
Replies
3
Views
281
ChetanPuri
C
S
  • Solved
Calculate Backdated dates
Replies
5
Views
290
Joe4
Joe4

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

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