[Solved] Count visible rows with criteria

[toshie]

Hey peeps, appreciate any help given in advance!

I have a what looks like a simple problem but have been searching around and cracking my head on resolving it.
I will like to calculate some data results where the formula will calculate the amount of cells from the column that is more than 0 after being applied some filters.

Example using the table data:

Temp.JPG (33.28 KiB) Viewed 656 times

Lets say when I apply filter to show only SG Time = PM, what formula can I use to calculate the number of visible cells for "Trade Outcome" that are more than 0? I don't want the sum of the "Trade Outcome" results, just the number of visible cells that fits criteria of "Trade outcome" more than 0.

Thanks for any help again!

[MrProgrammer]

Hi, and welcome to the forum.

I will like to calculate some data results where the formula will calculate the amount of cells from the column that is more than 0 after being applied some filters.

The only function I know of which can detect if a row has been filtered is SUBTOTAL with first operand 3. You can use that with SUMPRODUCT to get your count. I think you'll need to use an auxillary column to hold the SUBTOTAL formulas.

I don't know any way for a formula to know whether an empty filtered cell is visible.

Edit: Use an auxillary column with formula =ROW() and have SUBTOTAL check that to determine which rows are filtered.

[Tutorial] The SUMPRODUCT function

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet document.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[toshie]

Hi MrProgrammer,

Thanks for coming forward with help and sure, I have attached a simple stripped down spreadsheet for troubleshooting.

Returns calculatons - forum.ods

(11.82 KiB) Downloaded 16 times

Most of the formulas I wanted are in. Left the last part which is the "Filtered Wins".

Basically, "Filtered Wins" should be able to calculate how many visible rows left with positive "Trade outcome" after being applied filter for "SG Time".
Criteria is (Trade outcome > 0).

The closest I can get is total number of visible rows which is shown in the "After Filtered Sample Size" field using subtotal.

Cheers!

[MrProgrammer]

Thank you for the attachment.

Basically, "Filtered Wins" should be able to calculate how many visible rows left with positive "Trade outcome" after being applied filter for "SG Time".
Criteria is (Trade outcome > 0).

I added formulas in columns I and J. You can use Format → Columns → Hide on them if you don't want to see them. Column J tells the formulas in F10 and F11 if the filtered row is visible. I provided a link to the SUMPRODUCT tutorial in my earlier post.

202410180921.ods

(15.85 KiB) Downloaded 17 times

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[toshie]

Thank you for the attachment.

Basically, "Filtered Wins" should be able to calculate how many visible rows left with positive "Trade outcome" after being applied filter for "SG Time".
Criteria is (Trade outcome > 0).

I added formulas in columns I and J. You can use Format → Columns → Hide on them if you don't want to see them. Column J tells the formulas in F10 and F11 if the filtered row is visible. I provided a link to the SUMPRODUCT tutorial in my earlier post.202410180921.ods


If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

Thank you so much. Yes the issues is resolved.
I will further study what SUMPRODUCT can do!