subtotal with criteria after filtering

[Karolien_Abts]

Hi all,

Hope someone here has the solution for the issue I'm facing.

I've been asked to generate the following (see picture enclosed): data to be presented line by trial, with for each trial the % area and % unck (column R) at a certain timing presented and at the bottom with for the untreated the trial count, average, min and max value based on the % area values and for the other treatments (A, B, C) the same but based on the %unck.

Usually we only present 1 line per trial and then subtotal works perfect, but now with this additional criteria I'm stuck.

I want to prevent that I need to manually select all cells I want to subtotal (like now done enclosed) or prepare separate sheets for each possible filter combination to use 'averageif' as it is not sure yet which data combination we will present.

Any suggestions?

Thanks,

Karolien

[#2 · Accepted solution]

@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.

[#3]

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