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

  • Thread starter Thread starter Stiggy
  • Start date Start date
S

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

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

Hope this helps!
 
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
 
Similar to the above problem I want to use the SUBTOTAL command with this formula =COUNTIF(G20:G20008,"<"&G3).... Can anyone help
 
Back
Top