SUBTOTAL and COUNTIF (or SUMIF) combination

D

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

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"))
 
T

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top