How to make "countif" on a filtered excel-list? i.e. like subtotal

[Stiggy]

I have a filtered list of data where one column contains either of two text
values say "A" or "B".

When using filters on other columns I would like to att a counter above the
list that always show the number of "A" and "B" rows in the filtered data.

I do not want to add additional columns.

I cannot use Countif() since that doesnt bother about the filters.
If I use subtotal(3;...) it cares about the filters, but then I can only get
the sum of rows containing "A"'s and "B"'s...

Is this at all possible to achieve without macros or custom functions?

 

[Bernie Deitrick]

Stiggy,

1) Filter once on A, then again on B.

2) Use SUMPRODUCT functions with sections to replicate the filtering.

=SUMPRODUCT((A1:A100 = "A")*(B1:B100 = "Stiggy"))


HTH,
Bernie
MS Excel MVP

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(R
ange="A"))

Hope this helps!

 

[Somelad]

countif as subtotal

Hi

I can't get your formula to work.
Can you help?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(Range="A"))

My data is all in column E and I want to count how many A's there are in the column when filted (from another column)
Is there a simple way to do this?

Thanks in advance for your help

 

[Mark Powell]

Similar to the above problem I want to use the SUBTOTAL command with this formula =COUNTIF(G20:G20008,"<"&G3).... Can anyone help