Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re: SUBTOTAL and COUNTIF (or SUMIF) combination

4,393 views
Skip to first unread message

T. Valko

unread,
Feb 13, 2008, 5:12:29 PM2/13/08
to
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" <D...@discussions.microsoft.com> wrote in message
news:648E3E1B-7712-4307...@microsoft.com...
> 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

unread,
Feb 18, 2011, 8:15:30 AM2/18/11
to
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))

> On Wednesday, February 13, 2008 3:05 PM DK wrote:

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


>> On Wednesday, February 13, 2008 5:12 PM 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


>>> On Monday, July 19, 2010 11:51 AM Chris John wrote:

>>> I would like to use that function listed above to count the blanks of my data with filters but I can't seem to get it to work. My data is as follows:
>>>
>>>
>>>
>>> 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

mish...@gmail.com

unread,
Aug 14, 2015, 5:24:42 PM8/14/15
to
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?

Claus Busch

unread,
Aug 15, 2015, 2:14:19 AM8/15/15
to
Hi,

Am Fri, 14 Aug 2015 14:24:38 -0700 (PDT) schrieb mish...@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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
0 new messages