Register
Log In
Unanswered
Home
July 28, 2017
Offline
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
See AnswerJuly 28, 2017
Offline
see attachment for example
Dashboards
June 25, 2016
Offline
See if this helps.
Answers Post
July 28, 2017
Offline
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
Dashboards
June 25, 2016
Offline
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
December 5, 2016
Offline
Sunny,
What is syntex WEEKNUM(A1,2) meaning?
Dashboards
June 25, 2016
Offline
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 |
December 5, 2016
Offline
Thanks, Sunny, the very and precise explanation. You are brilliant, and superb, above all, no selfish heart but to share
with us the invaluable wholeheartedly.
July 28, 2017
Offline
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
Dashboards
June 25, 2016
Offline
Hi Chris
You can try using a PivotTable to create a PivotChart.
Refer attachment.
Sunny
Dashboards
June 25, 2016
Offline
Hi Chris
I have redone everything using a Pivot Table.
The only disadvantage is you cannot show the monthly total together with the week total.
If you need both then my previous post #10 should be the way to go.
Sunny
July 28, 2017
Offline
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.
Most Users Ever Online: 57
Currently Online:
21 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
