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"))
--
Biff
Microsoft Excel MVP

Post by 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.

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

Post by 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.

Post by T. Valko
A1:B1 = column headers
A2:B11 = data
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="A"))
--
Biff
Microsoft Excel MVP

Headers K26:T26
Data G26:T818
Thanks
Submitted via EggHeadCafe
ASP.NET- How to Raise Custom Events from a UserControl
http://www.eggheadcafe.com/tutorials/aspnet/d5f6cb27-dc1b-4233-86c0-aa8437138e47/aspnet-how-to-raise-custom-events-from-a-usercontrol.aspx

[m***@gmail.com]

Hi there,

How about if you wanted to count the blank cells in B2:B11 where A2:A11="cond"? Any suggestions on how to do that?

Post by T. Valko
A1:B1 = column headers
A2:B11 = data
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="A"))
--
Biff
Microsoft Excel MVP

Post by 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.

[Claus Busch]

Hi,

Post by m***@gmail.com
How about if you wanted to count the blank cells in B2:B11 where A2:A11="cond"? Any suggestions on how to do that?

try:

=COUNTIFS(A2:A11,"cond",B2:B11,"")

Regards
Claus B.