SUBTOTAL and COUNTIF (or SUMIF) combination
 

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.

SUBTOTAL and COUNTIF (or SUMIF) combination
 

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


"DKS" wrote in message
...
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.

SUBTOTAL and COUNTIF (or SUMIF) combination
 

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?

On Wednesday, February 13, 2008 at 4:12:29 PM UTC-6, T. Valko wrote:
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


"DKS" wrote in message
...
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.

SUBTOTAL and COUNTIF (or SUMIF) combination
 

Hi,

Am Fri, 14 Aug 2015 14:24:38 -0700 (PDT) schrieb :

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional