SUBTOTAL and COUNTIF (or SUMIF) combination

[DKS]

Hi,

I would like to use the COUNTIF (or SUMIF) only for filtered rows. Thus
somewhere a SUBTOTAL kind of functionality but for COUNTIF.

Is it possible to simulate (maybe with array formulas)?

Many thanks in anticipation.

 

[T. Valko]

A1:B1 = column headers
A2:B11 = data

Do a "countif" on B2:B11 = "A":

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="A"))

 

[Thomas Keddy]

I don't understand the formula, but it works great for counting filtered data!!! I changed the data "A" in the example to a cell containing a value (string) I wanted to count. Setting the data range to match mine was easy.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(T$5:T$885,ROW(T$5:T$885)-ROW(T$5),0,1)),--(T$5:T$885=S889))