Count if with filters

[lcohenvb]

I have a worksheet with 19 columns and, at the top, I have a summary table with "Countif" statements. I have added filters and I want the "countif" results to change depending on how the user changes the filter on any one of the 19 columns. I know "subtotal" will change depending on visible cells but I cannot get the "Countif" results to change. Is it possible?

Thanks for any comments. I tried to search the message boards to see if this has come up before but I didn't see a related message.

Lynn

 

[jamtay317]

Aladin, can you please explain what you did here? as I worked on this one for about 30 min and couldn't figure it out.

 

[Aladin Akyurek]

Aladin, can you please explain what you did here? as I worked on this one for about 30 min and couldn't figure it out.

In:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($R$11,ROW($R$11:$R$85270)-ROW($R$11),0)),--($R$11:$R$85270=H2))

SubTotal runs Counta (<-- 3) on the subreferences OFFSET creates [OFFSET(R11,0),OFFSET(R11,1),OFFSET(R11,2)...) that are visible, yielding a reference of evaluations consisting of 1's and 0's (1 if there is an item in the visible subreference). The latter is then multiplied with the reference the --($R$11:$R$85270=H2) evaluation in turn creates. The sum of multiplications so obtained is the count we require.