see attachment for example

See if this helps.

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

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

Sunny,

What is  syntex  WEEKNUM(A1,2) meaning?

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.

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.

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

Hi Chris

You can try using a PivotTable to create a PivotChart.

Refer attachment.

Sunny

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

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.

Hi Chris

See if this helps. The dates are auto calculated. You only need to change the product to filter. Enter an asterisk for all products.

Sunny