Active Member
July 28, 2017
Hello, for my reporting I need a segmented summation of data on a weekly and monthly basis (see example).
If I enter a new line for an invoice, the sum of the current week and current month should automatically increase
(not the sum of the week before or the month before).
In the following week it should start from zero, same is valid for the following month, i.e. I do not need an ongoing
summation, but instead a summation that recognizes a change in the week and a change in the month. I also
want to use filters and this should not effect the result of the summation. I would prefer a solution based on pure excel
commants without any macros or pivot tables.
Any ideas.
Regards,
chris
VIP
Trusted Members
June 25, 2016
Active Member
July 28, 2017
Hey SunnyKow,
thank you very much. Good solution. The only problem is that when I filter for a product the summations are not corrrect.
So if it would work with filter also, this would solve my issue perfectly. I have an example attached. Do you think the
function SUBTOTAL() could help here ?
Regards
Chris
VIP
Trusted Members
June 25, 2016
Hi Chris
I am using a helper column with SUBTOTAL. Please note that I am unable to use the SUBTOTAL for the entire Helper column because if you filter, the last row with the SUBTOTAL will ALWAYS be displayed. I have never encountered this before but maybe that is the way the filter works, always display the last SUBTOTAL in a filtered list. As a workaround, I need to determine if the row is the last row (by checking the Amount column if there is a value in the cell below). If it is the last row then don't use SUBTOTAL but refer directly to the cell.
Hope this helps.
Sunny
VIP
Trusted Members
June 25, 2016
Hi David
WEEKNUM(A1,2) will give you the week number of a date in a year. There are approximately 52 weeks in a year.
The 2 indicate what is the starting day of the week. In this case the 2 indicates the week starts from Monday. See example below.
If your week starts from Sunday, then choose 1.
Date | Day | Week Number |
01/01/2017 | Sunday | 1 |
02/01/2017 | Monday | 2 |
03/01/2017 | Tuesday | 2 |
04/01/2017 | Wednesday | 2 |
05/01/2017 | Thursday | 2 |
06/01/2017 | Friday | 2 |
07/01/2017 | Saturday | 2 |
08/01/2017 | Sunday | 2 |
09/01/2017 | Monday | 3 |
10/01/2017 | Tuesday | 3 |
11/01/2017 | Wednesday | 3 |
12/01/2017 | Thursday | 3 |
13/01/2017 | Friday | 3 |
14/01/2017 | Saturday | 3 |
15/01/2017 | Sunday | 3 |
16/01/2017 | Monday | 4 |
17/01/2017 | Tuesday | 4 |
Active Member
July 28, 2017
Hello again SunnyKow,
respect, this is a very intelligent solution. To be honest, I do not understand why it works, but it works....
It seems to be sufficient to use subtotal() only in cell F18 to make it work. What I do not unsterstand is
the following: If I filter for produkt B, F18 is not part of the summation but it seems to have an impact
on the calculations.
There is one wish left which would make the diagramms more clear. Any idea how the diagram or the
column F (which is basis of the diagram) can be modified in such a way that only the last bar that shows
the complete summation for each week is visible (at the moment every line is visible that belongs to a
week). I added an example with filter A switched on.
CU
chrisk
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
Active Member
July 28, 2017
Hello,
Thanks again for your input. The problem I see with pivot tables is the following.
The excel sheet I use is very complex (many additional columns and calculations). The invoice list is only a small extract of it.
I need to report daily, weekly and monthly. The lines are growing every during the day, so that I expect several hundred lines
every month.
With the pivot table my understanding is, that I have to create it manuelly every time I need to report the results. What
I need is an ongoing automatic calculation that adapts itself if the growing data base. Currently I use dynamic diagrams
for that. Do you think there is away to solve this only based on standard excel formulars that identify the last line of a
day/week/month (because that line contais the sum I need) and that shows only this last line in a diagram ?
Looking forward to hearing from you.
1 Guest(s)