Calculations of a filtered column

[opa1]

Hi,

I would like to have statistics, which I can calculate in a non-filtered column, also calculated in a filtered column.
I have experimented with SUMPRODUCT, SUBTOTAL etc, but just can’t come up with the right formula for quite some time and hope you can help me.

My raw data is in A2:C6.
In E9:I20 I have calculations for the non-filtered column and would now like to have the same statistics in case I filter for example column A by “Mon”.

I found a solution in an excel forum, but unfortunately this will not work for LibreOffice as the function “SEQUENCE” is not supported by Calc.

J9 =SUM(($B$2:$B$6>=--"00:00:00")*($B$2:$B$6<--"01:00:00")*SUBTOTAL(3;INDIRECT("A"&SEQUENCE(5;;2))))

K9 =SUM(($C$2:$C$6>=0)*($B$2:$B$6>=--"0:0:0")*($B$2:$B$6<--"01:00:00")*SUBTOTAL(3;INDIRECT("A"&SEQUENCE(5;;2))))

L9 =K9/J9

M9 =SUM(C2:C6* (B2:B6>=--"00:00:00")*(B2:B6<--"01:00:00")*SUBTOTAL(3;INDIRECT("A"&SEQUENCE(5;;2))))

Also the calculations for H17 and H18 are still missing.

Thank you very much in advance fpr your help!

filtered_stats.ods (15.8 KB)

[Vrolik]

Berechnungen in gefilterter Liste durchführen - LibreOffice-Forum

[dscheikey]

Hi, @opa1
I have created a series of formulas that should solve your problem. See also the attached result sheet.
The secret is that with

=SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$6)-ROW($B$2),0))

you get a list with TRUE and FALSE if a row is filtered or not. You can then use SUMPRODUCT() to calculate your statistics.

For the consecutive wins filtered evaluation, I needed two additional auxiliary columns.

filtered_stats.ods (19,2 KB)

[opa1]

Hi @dscheikey,

thank you so much!

Everything is working except the consecutive wins/losses filtered when I enter more data.
I entered 5 losses in a row for Friday and in unfiltered and filtered form (by Friday) the consecutive losses filtered is counted as 4 although 5 is correct.

I have attached the updated file.
Do you have any idea what is wrong?

Thank you very much in advance and have a great day!
filtered_stats_new.ods (24.9 KB)

[dscheikey]

An error in cell D1. Please copy down from cell D17!

[opa1]

Thank you, this was indeed the problem with this sample data.
I have now copied my actual data from my main spreadsheet into the spreadsheet and now the issue is reproducible. If I filter column A by “buy”, then the consecutive losses are counted as 4, but when I count manually, there are 5.
The result of E24 does not look right to me in the filtered view.
Do you have any idea?
Thank you very much in advance!
filtered_stats_new2.ods (29.1 KB)

Edit: also the result of E34 is looking strange after the first win.

[dscheikey]

Hi @opa1, there was still an error in my Counter1 column. I think I have now corrected and straightened everything out.

filtered_stats_new2.ods (32,3 KB)

[opa1]

Thank you, this is working now like a charm!