sum the current week on each day

[trisoldee]

Hello,

I'm trying to add a column to give me the the total of the current week for each day of that week. I've tried a few different things but none of them are working. Any help would be greatly appreciated.

Thank you.


Book1.xlsx

[tim201110]

=sum(index($c$1:c2,aggregate(14,6,row($a$2:a2)/(weekday($a$2:a2,2)=1),1)):c2)

[AlKey]

Maybe this with a helper column in column E.
1. Enter formula in E2 and copy down

Please Login or Register  to view this content.

2. Enter formula in F2 and copy down

Please Login or Register  to view this content.

v	A	B	C	D	E	F
1	Date	Branch	Target	WeeklyTarget	Helper	Results
2	7/2/2018	701	98,793	317,613	27
3	7/3/2018	701	50,000	317,613	27
4	7/4/2018	701	68,820	317,613	27
5	7/5/2018	701	50,000	317,613	27
6	7/6/2018	701	50,000	317,613	27
7	7/7/2018	701	-	317,613	27	1,905,678.00
8	7/8/2018	701	-	317,613	28
9	7/9/2018	701	50,000	409,062	28
10	7/10/2018	701	50,000	409,062	28
11	7/11/2018	701	71,466	409,062	28
12	7/12/2018	701	128,077	409,062	28
13	7/13/2018	701	109,519	409,062	28
14	7/14/2018	701	-	409,062	28	2,771,985.00
15	7/15/2018	701	-	409,062	29
16	7/16/2018	701	215,286	563,414	29
17	7/17/2018	701	50,000	563,414	29
18	7/18/2018	701	98,128	563,414	29
19	7/19/2018	701	50,000	563,414	29
20	7/20/2018	701	50,000	563,414	29
21	7/21/2018	701	50,000	563,414	29	3,789,546.00
22	7/22/2018	701	50,000	563,414	30	563,414.00

[trisoldee]

I figured out another way that may help, I forgot to mention that I'm using a power pivot, I was working on getting the week range in on my date table to only show for the current week.

I'll let you know how it goes.

Thank you all for the help.

[trisoldee]

Maybe this with a helper column in column E.
1. Enter formula in E2 and copy down

Please Login or Register  to view this content.

2. Enter formula in F2 and copy down

Please Login or Register  to view this content.

v	A	B	C	D	E	F
1	Date	Branch	Target	WeeklyTarget	Helper	Results
2	7/2/2018	701	98,793	317,613	27
3	7/3/2018	701	50,000	317,613	27
4	7/4/2018	701	68,820	317,613	27
5	7/5/2018	701	50,000	317,613	27
6	7/6/2018	701	50,000	317,613	27
7	7/7/2018	701	-	317,613	27	1,905,678.00
8	7/8/2018	701	-	317,613	28
9	7/9/2018	701	50,000	409,062	28
10	7/10/2018	701	50,000	409,062	28
11	7/11/2018	701	71,466	409,062	28
12	7/12/2018	701	128,077	409,062	28
13	7/13/2018	701	109,519	409,062	28
14	7/14/2018	701	-	409,062	28	2,771,985.00
15	7/15/2018	701	-	409,062	29
16	7/16/2018	701	215,286	563,414	29
17	7/17/2018	701	50,000	563,414	29
18	7/18/2018	701	98,128	563,414	29
19	7/19/2018	701	50,000	563,414	29
20	7/20/2018	701	50,000	563,414	29
21	7/21/2018	701	50,000	563,414	29	3,789,546.00
22	7/22/2018	701	50,000	563,414	30	563,414.00

Sorry, let me clarify.

Col D is what I'm trying to get. I filled it in manually to show what I'm looking for.

[tim201110]

with a helper column

why?
=SUM($C$2:C2*(ISOWEEKNUM($A$2:A2)=ISOWEEKNUM(A2))) CSE

[trisoldee]

=sum(index($c$1:c2,aggregate(14,6,row($a$2:a2)/(weekday($a$2:a2,2)=1),1)):c2)

This works to give the cumulative total for each week, I'm looking for the full total of the week to be repeated on each row. Col D in the attachment is what I'm looking for as my end result.

Let me know what you think and thank you.

[trisoldee]

why?
=SUM($C$2:C2*(ISOWEEKNUM($A$2:A2)=ISOWEEKNUM(A2))) CSE

This is also giving me the cumulative total.

I'm looking for the full week total because I'm trying to build a pivot table and get it to show me the current day's total as well as the current week's total.

Col D is essentially my helper column.

[AlKey]

Try this
Enter in D2

Please Login or Register  to view this content.

[AlKey]

***replace for helper column with =WEEKNUM(A2-1) and use formula from post #9

Please Login or Register  to view this content.

[trisoldee]

Try this
Enter in D2

Please Login or Register  to view this content.

So helper col for the weeknum, looks good.

Thank you very much.

[trisoldee]

***replace for helper column with =WEEKNUM(A2-1) and use formula from post #9

Please Login or Register  to view this content.

Sorry, one more caveat, the dates repeat in the table for more than one Branch.

[AlKey]

Then use this:

Please Login or Register  to view this content.

[trisoldee]

Then use this:

Please Login or Register  to view this content.

Fantastic!!!

Thank you.