Forum Discussion
subtotal with criteria after filtering
Karolien_Abts Oh my, that's not a very well designed schedule, but I assume you are stuck with it.
Due to the double row set-up for %AREA and %UNCK you can't use the SUBTOTAL functions to count, average, min and max alternating visible rows, other than in the way you have it already.
Add a column to the data (may be hidden) with =SUBTOTAL(103,A4) and copy it down. That will create a column of 1's and 0's now you can use regular COUNTIFS, AVERAGEIFS, MINIFS and MAXIFS where you set the criteria to be only for rows where "Visible" = 1, and "Rating Unit" = %AREA in the Untreated column and %UNCK in all other columns.
See attached.
Karolien_Abts Oh my, that's not a very well designed schedule, but I assume you are stuck with it.
Due to the double row set-up for %AREA and %UNCK you can't use the SUBTOTAL functions to count, average, min and max alternating visible rows, other than in the way you have it already.
Add a column to the data (may be hidden) with =SUBTOTAL(103,A4) and copy it down. That will create a column of 1's and 0's now you can use regular COUNTIFS, AVERAGEIFS, MINIFS and MAXIFS where you set the criteria to be only for rows where "Visible" = 1, and "Rating Unit" = %AREA in the Untreated column and %UNCK in all other columns.
See attached.
- It is indeed a horendous table, but specific client request so no other option.
Thanks for the advice, that works indeed very well like this
